首页 > 代码库 > mysql主从和主主备份

mysql主从和主主备份

  



wKioL1QX1QTybsV8AABAsFd9QfY119.jpg

1.创建一个数据库用于测试同步;

mysql> create database dragon;

Query OK, 1 row affected (0.04 sec)


mysql> use dragon

Database changed

mysql> create table user(id int(5),name char(20));

Query OK, 0 rows affected (0.17 sec)


mysql> quit


2.修改主库my.cnf主要设置个不一样的ID,以及同步的数据库的名字,我一般用vim 来完成,vim /etc/my.cnf 在[mysqld]中加入内容如下

server-id=1

log-bin=binlog

binlog-do-db=dragon  #指明同步那些数据库

replicate-ignore-db=mysql  #指明不同步那些数据库

replicate-ignore-db=information_schema

port=3306


重启服务使得配置文件生效

[root@localhost ~]# service mysqld restart

Shutting down MySQL.                                       [确定]

Starting MySQL..                                           [确定]

[root@localhost ~]# mysql -uroot -p123


3.登录主库赋予从库权限账号,允许用户在主库上读取日志(用户名: admin密码:123456)

[root@localhost ~]# mysql -uroot -p123

mysql> grant replication slave on *.* to ‘admin‘@‘10.10.10.2‘ identified by ‘123456‘;

Query OK, 0 rows affected (0.01 sec)


在slave上测试账号是否能够被登陆:

[root@localhost ~]# mysql -uadmin -p123456 -h 10.10.10.1 -S/tmp/mysql.sock

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.5.33-log Source distribution


Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.


Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql> 


4.slave机器获取mastre快照。有两种方法:一种是进入/var/lib/mysql/用tar方法备份

[root@localhost mysql]# cd /usr/local/mysql/data/

[root@localhost data]# tar czvf dragon.tar.gz dragon/

dragon/

dragon/db.opt

dragon/user.frm

[root@localhost data]# scp dragon.tar.gz 10.10.10.2:/usr/local/mysql/data

The authenticity of host ‘10.10.10.2 (10.10.10.2)‘ can‘t be established.

RSA key fingerprint is 01:77:02:41:8b:f3:86:3e:e9:58:b3:f2:91:34:91:90.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added ‘10.10.10.2‘ (RSA) to the list of known hosts.

root@10.10.10.2‘s password: 

dragon.tar.gz                                 100%  416     0.4KB/s   00:00   


[root@localhost data]# tar zxf dragon.tar.gz 

[root@localhost data]# service mysqld restart


5.在master1上查看dragon数据文件的信息(记录file、position,从库设置将会用到),并且锁住表;

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.01 sec)


mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000024 |      563 | dragon       |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)


mysql> unlock tables;


6.修改slave2的配置文件vim /etc/my.cnf,把server_id改为2或者添加server_id=2,总之server_id是一个与master的server_id不同数值即可,如果server_id与master相同会出现1593这个错误,可以进入数据库通过" show variables like "server_id""来查看servver_id是否被更改. 进入slave的数据库进行下一步同步配置。


mysql> slave stop;

Query OK, 0 rows affected (0.00 sec)


mysql>change master to master_host=‘10.10.10.1‘,master_user=‘admin‘,master_password=‘123456‘,master_log_file=‘mysql-bin.000024 ‘,master_log_pos=563;


mysql> slave start;

Query OK, 0 rows affected (0.00 sec)


重启一下数据库:service mysqld restart;再进入数据库查看同步是否成功,slave_io_running和slave_sql_running均为YES。

mysql>show slave status\G;

Slave_IO_Running: Yes


Slave_SQL_Running: Yes


在master1上创建一个表,再到slave上查看表有没有被同步到。

mysql> create table dragonttest(id int(4),name char(10));

Query OK, 0 rows affected (0.18 sec)


mysql> 


mysql> show tables;

+------------------+

| Tables_in_dragon |

+------------------+

| dragonttest      |

| user             |

+------------------+

2 rows in set (0.00 sec)



配置主主备份:

  

    这里才是主主复制的开始,其实方法很简单,我们之前可以实现主从复制也就是说,再把从做为主,主再做为从,就实现主主复制了,我是在主从复制完成后在此基础上去完成主主复制的。


1、编辑之前的从服务器,也就是现在的master2

vim /etc/my.cnf

 加入如下内容:

binlog-do-db=dragon


replicate-ignore-db=test


replicate-ignore-db=mysql


replicate-ignore-db=information_schema


port=3306


2.重启服务

 service mysqld restart

 

3、登录master2(master2 Ip地址为10.10.10.2)数据库赋予master数据库权限账号,允许用户在master主库(ip 为10.10.10.1)上读取日志(用户名: admin密码:123456)

mysql -uroot -p123

grant replication slave on *.* to ‘admin‘@‘10.10.10.1‘identified by ‘123456‘;

为验证账号我们可以在master1的机器上用命令作如下测试

mysql -u admin -p -h 10.10.10.2 -S/tmp/mysql.sock


4、master2上登录数据库记录file 和position

mysql> show master status;


+---------------+----------+--------------+------------------+


| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |


+---------------+----------+--------------+------------------+


| binlog.000004 |      106 | dragon   |                  |


+---------------+----------+--------------+------------------+


1 row in set (0.00 sec)


5、在master上登录数据库做如下配置

mysql>stop slave;

mysql>CHANGE MASTER TO MASTER_HOST=‘10.10.10.2‘,MASTER_USER=‘admin‘,MASTER_PASSWORD=‘123456‘,MASTER_PORT=3306,MASTER_LOG_FILE=‘binlog.000004 ‘,MASTER_LOG_POS=106;        (请注意CHANGE 到pos=106为一行命令)

mysql>start slave;

mysql>show slave status\G;

Slave_IO_Running: Yes


Slave_SQL_Running: Yes

看到上两个进程为数据库与另一边的master 已经建立连接


本文出自 “龙爱雪琪” 博客,请务必保留此出处http://dragon123.blog.51cto.com/9152073/1553276

mysql主从和主主备份