江苏省吴江市双板桥路3号兴源网络

0512-63028342

ly@wjxy.net.cn

宝塔LNMP mysql主从同步详细教程

主:
/etc/my.cnf
server_id = 1 #唯一标识,主库从库不能重复
log_bin = mysql-bin #开启日志
binlog_format=MIXED    #日志记录的格式
max_binlog_size = 512M    #单个日志文件最大
expire_logs_day = 3 #日志有效期(天)
binlog_do_db = test1,test2 #日志记录那些数据库
binlog_ignore_db = mysql,performance_schema,information_schema #日志记录忽略那些数据库的
service mysql restart
mysql -p密码
grant replication slave on . to 'wjxy_net'@'%';
flush tables with read lock;
mysqldump -uroot -p wjxy_net>/root/wjxy_net.sql;
show master status; 记下 Position 和 File 的值

从库配置/etc/my.cnf:
server_id = 2
log_bin = mysql-bin #开启日志
binlog_format=MIXED #日志记录的格式
max_binlog_size = 512M    #单个日志文件最大
expire_logs_day = 3    #日志有效期(天)
replicate_do_db = test1,test2 #是在slave上配置,指定slave要复制哪个库
replicate-ignore-db=mysql,performance_schema,information_schema #是在slave上配置,指定slave要忽略哪个库
relay_log_recovery = 1    #从库建议开启,有利于数据一致性
log_slave_updates = 1    #如果从库还会用做主库,建议开启
service mysql restart
scp root@主库IP:/root/wjxy_net.sql /root/wjxy_net.sql
mysql -p密码
stop slave;
mysql -uroot -p wjxy_net</root/wjxy_net.sql;
change master to master_host=’wjxy.net.cn’, master_user=’wjxy_net’, master_password=’xxxxxx’, master_port=3306, master_log_file=’mysql-bin.000023′, master_log_pos=2720; 注意修改最后两项
slave start;
从库执行: show slave status 或者show slave status\G; 查看是否正常
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_Log_File: mysql-bin.000023
Relay_Master_Log_File: mysql-bin.000023
Read_Master_Log_Pos: 2720
Exec_master_log_pos: 2720
————————————
最后回到主库输入 unlock tables;
show master status; 记下 现在的Position 和 File 的值
从库执行 show slave status\G; 比对同步情况

set global read_only=1; 从数据库设置只读 #1是只读,0是读写(具体参考这篇文章:设置mysql数据库为只读)

show global variables like "%read_only%";查询只读设置是否失效

——————————————————————————-分割线———————————————————

1、安装好主数据库和从数据库,这个大家肯定都会,如果不是很明白,可以参考我前面的安装教程。

例子:

  假如我需要同步test1、test2数据库 

  系统:centos7

  主库主机:192.168.1.252 

  从库主机:192.168.1.251   

  端口都是:3306

2、主数据库基本配置

  (1)、打开主数据的配置文件/etc/my.cnf,添加修改这几项

server_id = 1 #唯一标识,主库从库不能重复
log_bin = mysql-bin #开启日志
binlog_format=MIXED    #日志记录的格式
max_binlog_size = 512M    #单个日志文件最大
expire_logs_day = 3 #日志有效期(天)
binlog_do_db = test1,test2 #日志记录那些数据库
binlog_ignore_db = mysql,performance_schema,information_schema #日志记录忽略那些数据库的

(2)、重启mysql (service mysql restart)
(3)、在主库创建一个用于同步的用户(当然也可以不创建,直接用已有的,不过为了安全建议还是创建一个)(这一步可以忽略,直接使用宝塔面板上的用户名密码)
 grant replication slave on *.* to 'slave'@'192.168.1.253' identified by '123456';
   这个slave用户密码为123456,但有同步的权限,且只能在192.168.1.253上连接。

grant replication slave on . to 'wjxy_net'@'%'; 这里我用的是这个命令

  如果主库里已经数据库数据了,那还得进行以下操作(root密码并非系统root密码,而是在宝塔里面数据库菜单下获取)

mysql -u root -p 或者 mysql -u root -p密码 //p和密码之间不可以有空格

 刷新表然后锁表(只允许查数据不允许写数据):

flush tables with read lock;

    然后备份要同步的数据库的数据,然后拷贝到从库里面

mysqldump -uroot -p test1>test1.sql;
mysqldump -uroot -p test2>test2.sql;
#从库要先创建数据库test1和test2,然后导入数据
mysql -uroot -p test1<test1.sql;
mysql -uroot -p test1<test2.sql;

主库执行:show master status; 记下 Position 和 File 的值,等下进行从库配置要用到。

  +——————+———-+————–+——————+
  | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  +——————+———-+————–+——————+
  | mysql-bin.000023 | 2720 | test1,test2 | mysql 
  +——————+———-+————–+——————+

3、从库配置信息

  (1)、修改配置文件my.cnf’

server_id = 2
log_bin = mysql-bin #开启日志
binlog_format=MIXED #日志记录的格式
max_binlog_size = 512M    #单个日志文件最大
expire_logs_day = 3    #日志有效期(天)
replicate_do_db = test1,test2 #是在slave上配置,指定slave要复制哪个库
replicate-ignore-db=mysql,performance_schema,information_schema #是在slave上配置,指定slave要忽略哪个库
relay_log_recovery = 1    #从库建议开启,有利于数据一致性
log_slave_updates = 1    #如果从库还会用做主库,建议开启

  (2)、重启从库,执行如下命令(先停止同步,然后设置同步信息,master_log_file 和 master_log_pos就是我们主库上面的)

stop slave;
change master to
master_host=’192.168.1.252′,
master_user=’slave’,
master_password=’123456′,
master_port=3306,
master_log_file=’mysql-bin.000023′,
master_log_pos=2720;
slave start;

  (3)、从库执行:show slave status\G; 查看是否正常, 主要看这几项:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_Log_File: mysql-bin.000023
Relay_Master_Log_File: mysql-bin.000023
Read_Master_Log_Pos: 2720
Exec_master_log_pos: 2720

4、最后回到主库解锁表 

unlock tables;

5、主从不同步了解决办法

方法一:忽略错误后,继续同步
  该方法适用于主从库数据相差不大,或者要求数据可以不完全统一的情况,数据要求不严格的情况
  解决:
    stop slave; #表示跳过一步错误,后面的数字可变
    set global sql_slave_skip_counter =1;
    start slave;
  之后再用mysql> show slave status\G 查看:
  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes

方式二:重新做主从,完全同步
  该方法适用于主从库数据相差较大,或者要求数据完全统一的情况
  解决步骤如下:
    1.先进入主库,进行锁表,防止数据写入
    使用命令:
    mysql> flush tables with read lock;
    注意:该处是锁定为只读状态,语句不区分大小写
    2.进行数据备份
      #把数据备份到mysql.bak.sql文件
      mysqldump -uroot -p -hlocalhost > mysql.bak.sql
      这里注意一点:数据库备份一定要定期进行,可以用shell脚本或者python脚本,都比较方便,确保数据万无一失
    3.查看master 状态
      mysql> show master status;
      +——————-+———-+————–+——————————-+
      | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
      +——————-+———-+————–+——————————-+
      | mysqld-bin.000001 | 3260 | | mysql,test,information_schema |
      +——————-+———-+————–+——————————-+

    4.把mysql备份文件传到从库机器,进行数据恢复
      #使用scp命令
      [root@server01 mysql]# scp mysql.bak.sql root@192.168.128.101:/tmp/
    5.停止从库的状态
      mysql> stop slave;
    6.然后到从库执行mysql命令,导入数据备份
      mysql> source /tmp/mysql.bak.sql
    7.设置从库同步,注意该处的同步点,就是主库show master status信息里的| File| Position两项
      change master to master_host = '192.168.128.100', master_user = 'rsync', master_port=3306, master_password='', master_log_file = 'mysqld-bin.000001', master_log_pos=3260;
    8.重新开启从同步
      mysql> start slave;
    9.查看同步状态
      mysql> show slave status\G 查看:
      Slave_IO_Running: Yes
      Slave_SQL_Running: Yes

           10.主库解锁

    mysql> unlock tables;

6、pt-table-checksum 和 pt-table-sync

  这个两个软件也是很方便,大家可以去了解一下

  pt-table-checksum:在主(master)上通过执行校验的查询对复制的一致性进行检查,对比主从的校验值,从而产生结果。DSN指向的是主的地址,该工具的退出状态不为零,如果发现有任何差别,或者如果出现任何警告或错误,更多信息请查看官方资料。
  pt-table-sync: 高效的同步MySQL表之间的数据,他可以做单向和双向同步的表数据。他可以同步单个表,也可以同步整个库。它不同步表结构、索引、或任何其他模式对象。所以在修复一致性之前需要保证他们表存在。



发表评论

电子邮件地址不会被公开。 必填项已用*标注