Mysql的主从同步和双向同步
时间:2017-06-21 09:15 来源:linux.it.net.cn 作者:IT
Mysql的主从同步（新数据库）
数据库同步复制功能的设置都是在mysql的配置文件（liunx 下是为/etc/my.cnf）中体现的。主要工作原理为slave端记录并且执行master端的操作日志。
分别登录master机和slave机的mysql：mysql –u root –p
创建数据库：create database repl;
1、设置同步master
编辑配置文件 /etc/my.cnf 确保有如下行
server-id=1
binlog-do-db=repl //需要备份的数据库名,如果需要备份多个数据库，重复设置这个选项即可。
log-bin=mysql-bin //启动二进制日志系统
以下的几个参数可以不加：
log-err=my-err.log
log-bin=mysql-bin //启动二进制日志系统
binlog-ignore-db=mysql //不需要备份的数据库名，防止备份出错，可以把不备份的数据库写入这个参数中
log-slave-updates //这个参数是把更新的记录写到二进制文件中
slave-skip-errors //跳过错误，继续执行复制操作
创建一个mysql的账号为同步专用
mysql –u root –p
mysql> grant replication slave on *.* to 'gl'@'192.168.10.Slave' identified by '123456';
#grant replication slave on *.* to ‘用户名’@'主机’ identified by ‘密码’;
mysql> flush privileges; #刷新权限
重启master mysql
查看master状态
mysql>show master status;
查看进程：
mysql> show processlist;
3、设置同步slave
编辑配置文件
server-id=2
master-user=gl //同步账号
master-host=192.168.10.master //同步masterIP
master-password=123456 //同步密码
master-connect-retry=60 //心跳时间（库同步间隔）
master-port=3306 //master数据库的端口
replicate-do-db=repl //同步的数据库名
然后重启slave mysql
在slave机中进入mysql
mysql>start slave;
mysql>show slave status/G;
如果Slave_IO_Running、Slave_SQL_Running状态为Yes则表明设置成功。
查看slave状态
mysql>show master status;
查看进程：
mysql> show processlist;
我在第一次配的时候出现错误，查看日志为
error got fatal error：1236 binary log is not open from master where reading data from binary log
后来查找后知道为没有设置log-bin
即在master中my.cnf中设置log-bin=mysql-bin //启动二进制日志系统
mysql的主从同步（数据库已经运行一段时间）
1、设置同步master #同上
2、创建一个mysql账号为同步账号
3、给主服务器所有表加锁，禁止继续写入数据
mysql>FLUSH TABLES WITH READ LOCK；
mysql> show master status; ???? ???????? //记录复制启动断点，也可以使用 show master status/G
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 106 | backup | |
+------------------+----------+--------------+------------------+
//记录File: mysql-bin.000003 偏移量 Position 106
4、打包备份主服务器数据文件
cd /var/lib/mysql
tat –cvf repl.tar repl //打包需要备份的数据库
scp repl.tar 192.168.10.204:/var/lib/mysql? //将打包后的数据库传到备份服务器
5、主服务器表解锁
执行以下命令，解开主服务器的读锁，主服务器可以继续更新数据：
mysql> UNLOCK TABLES；
6、设置同步slave //同上3
7、在从服务器上恢复主备份文件
cd /var/lib/mysql
tar –xvf repl.tar
8、在从服务器mysql命令符下实现同步
若是查看不能正常同步时，需进行如下操作：
mysql> slave stop;mysql> change master to
mysql> master_host='10.10.0.244',
mysql> master_user='backup',
mysql> master_password='123456',
mysql> master_log_file='mysql-bin.000003', //主服务上的file
mysql> master_log_pos=106; //主服务器上的pos
mysql> slave start;
用show slave status/G;看一下从服务器的同步情况
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果都是yes，那代表已经在同步
往表里面写点数据测试一下看是否同步成功。
mysql的双向同步
配置双向同步方法就是在单向同步的基础上略加改动，即在从机上做主机配置，在主机上做从机配置。
在master上做从机配置
在配置文件中添加
master-host=192.168.10.204
master-user=gl
master-password=12345678
master-port=3306
master-connect-retry=60
replicate-do-db=repl
#service mysql restart 重启mysql
在slave上做主机配置
在配置文件中添加
binlog-do-db=repl
log_err=my-err.log
创建同步账号
mysql> grant replication slave on *.* to 'gl'@'192.168.10.237' identified by '12345678';
mysql> flush privileges;
在master上测试同步账号 mysql -h 192.168.10.204 -u gl -p
查看同步状态
在master上查看
mysql> slave stop;
mysql> slave start;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO
mysql> show slave status;
Empty set (0.00 sec)
此时master上slave不能启动，需要进行如下操作
查看slave上的master状态
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 106 | backup | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
在master上添加 change master to
mysql> change master to master_host='10.10.0.247', master_user='backup', master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=106;
Query OK, 0 rows affected (0.02 sec)
mysql> slave start;
Query OK, 0 rows affected (0.00 sec)
查看master的 slave状态
Slave_IO_Running: Yes Slave_SQL_Running: Yes //此时正常
（出现error 1200的错误也可修改数据库下的master.info文件
/usr/local/mysql/data # vim master.info
15
mysql-bin.000004 //master_log_file
204 //master_log_pos
10.10.0.247 //master_host
Backup //master_user
123456 //master_password
3306 //master-port
60 master_connect_retry
0 ）
(责任编辑：IT)
| Mysql的主从同步（新数据库） 数据库同步复制功能的设置都是在mysql的配置文件（liunx 下是为/etc/my.cnf）中体现的。主要工作原理为slave端记录并且执行master端的操作日志。 分别登录master机和slave机的mysql：mysql –u root –p 创建数据库：create database repl; 1、设置同步master 编辑配置文件 /etc/my.cnf 确保有如下行 server-id=1 binlog-do-db=repl //需要备份的数据库名,如果需要备份多个数据库，重复设置这个选项即可。 log-bin=mysql-bin //启动二进制日志系统 以下的几个参数可以不加： log-err=my-err.log log-bin=mysql-bin //启动二进制日志系统 binlog-ignore-db=mysql //不需要备份的数据库名，防止备份出错，可以把不备份的数据库写入这个参数中 log-slave-updates //这个参数是把更新的记录写到二进制文件中 slave-skip-errors //跳过错误，继续执行复制操作 创建一个mysql的账号为同步专用 mysql –u root –p mysql> grant replication slave on *.* to 'gl'@'192.168.10.Slave' identified by '123456'; #grant replication slave on *.* to ‘用户名’@'主机’ identified by ‘密码’; mysql> flush privileges; #刷新权限 重启master mysql 查看master状态 mysql>show master status; 查看进程： mysql> show processlist; 3、设置同步slave 编辑配置文件 server-id=2 master-user=gl //同步账号 master-host=192.168.10.master //同步masterIP master-password=123456 //同步密码 master-connect-retry=60 //心跳时间（库同步间隔） master-port=3306 //master数据库的端口 replicate-do-db=repl //同步的数据库名 然后重启slave mysql 在slave机中进入mysql mysql>start slave; mysql>show slave status/G; 如果Slave_IO_Running、Slave_SQL_Running状态为Yes则表明设置成功。 查看slave状态 mysql>show master status; 查看进程： mysql> show processlist; 我在第一次配的时候出现错误，查看日志为 error got fatal error：1236 binary log is not open from master where reading data from binary log 后来查找后知道为没有设置log-bin 即在master中my.cnf中设置log-bin=mysql-bin //启动二进制日志系统 mysql的主从同步（数据库已经运行一段时间） 1、设置同步master #同上 2、创建一个mysql账号为同步账号 3、给主服务器所有表加锁，禁止继续写入数据 mysql>FLUSH TABLES WITH READ LOCK； mysql> show master status; ???? ???????? //记录复制启动断点，也可以使用 show master status/G mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 106 | backup | | +------------------+----------+--------------+------------------+ //记录File: mysql-bin.000003 偏移量 Position 106 4、打包备份主服务器数据文件 cd /var/lib/mysql tat –cvf repl.tar repl //打包需要备份的数据库 scp repl.tar 192.168.10.204:/var/lib/mysql? //将打包后的数据库传到备份服务器 5、主服务器表解锁 执行以下命令，解开主服务器的读锁，主服务器可以继续更新数据： mysql> UNLOCK TABLES； 6、设置同步slave //同上3 7、在从服务器上恢复主备份文件 cd /var/lib/mysql tar –xvf repl.tar 8、在从服务器mysql命令符下实现同步 若是查看不能正常同步时，需进行如下操作： mysql> slave stop;mysql> change master to mysql> master_host='10.10.0.244', mysql> master_user='backup', mysql> master_password='123456', mysql> master_log_file='mysql-bin.000003', //主服务上的file mysql> master_log_pos=106; //主服务器上的pos mysql> slave start; 用show slave status/G;看一下从服务器的同步情况 Slave_IO_Running: Yes Slave_SQL_Running: Yes 如果都是yes，那代表已经在同步 往表里面写点数据测试一下看是否同步成功。 mysql的双向同步 配置双向同步方法就是在单向同步的基础上略加改动，即在从机上做主机配置，在主机上做从机配置。 在master上做从机配置 在配置文件中添加 master-host=192.168.10.204 master-user=gl master-password=12345678 master-port=3306 master-connect-retry=60 replicate-do-db=repl #service mysql restart 重启mysql 在slave上做主机配置 在配置文件中添加 binlog-do-db=repl log_err=my-err.log 创建同步账号 mysql> grant replication slave on *.* to 'gl'@'192.168.10.237' identified by '12345678'; mysql> flush privileges; 在master上测试同步账号 mysql -h 192.168.10.204 -u gl -p 查看同步状态 在master上查看 mysql> slave stop; mysql> slave start; ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO mysql> show slave status; Empty set (0.00 sec) 此时master上slave不能启动，需要进行如下操作 查看slave上的master状态 mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000004 | 106 | backup | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) 在master上添加 change master to mysql> change master to master_host='10.10.0.247', master_user='backup', master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=106; Query OK, 0 rows affected (0.02 sec) mysql> slave start; Query OK, 0 rows affected (0.00 sec) 查看master的 slave状态 Slave_IO_Running: Yes Slave_SQL_Running: Yes //此时正常 （出现error 1200的错误也可修改数据库下的master.info文件 /usr/local/mysql/data # vim master.info 15 mysql-bin.000004 //master_log_file 204 //master_log_pos 10.10.0.247 //master_host Backup //master_user 123456 //master_password 3306 //master-port 60 master_connect_retry 0 ） (责任编辑：IT) |