MySQL8.0主从同步

《MySQL8.0主从同步》

主机配置

查看bin-log的自增位置

#cd /var/lib/mysql/
//日志刷新
mysql> flush logs; //在原日志记录的基本上 追加一条新的记录,类似于断点
admin_masterdb  binlog.000001 
admin_masterdb  binlog.000001 binlog.000002

//查看最后一个bin-log日志
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000076 |   542666 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

//查看所有bin-log日志
mysql> show master logs;
+---------------+------------+-----------+
| Log_name      | File_size  | Encrypted |
+---------------+------------+-----------+
| binlog.000037 | 1073741954 | No        |
| binlog.000038 | 1073743014 | No        |
...........
| binlog.000074 |    9306376 | No        |
| binlog.000075 |   69894289 | No        |
| binlog.000076 |     629243 | No        |
+---------------+------------+-----------+

//清空所有的bin-log日志
mysql> reset master;
mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |      1960 | No        |
+---------------+-----------+-----------+

//查看指定bin-log日志

恢复操作

//Bin-log 日志恢复
# mysqlbinlog binlog.000027 --start-position="100" --stop-position="50" --start-date="2012-01-04 12:17:50" --stop-date="2012-01-04 18:17:50" | mysql –uroot –p -f masterdb
--start-position 开始位置
--start-date开始时间
//|mysql:将所得到的结果以mysql命令执行;

//数据备份 mysqldump , 导出sql记录
# /usr/bin/mysqldump -uroot -p'xxx' -f masterdb > /storage/sql_backup/$(date +%Y-%m)_book_masterdb.sql

//数据导入 mysqldump , 导入sql记录
# mysql –uroot –p -f masterdb < /storage/sql_backup/xxx_test.sql;

//远程操作
# scp -r root@192.168.0.80:/tmp/test01.sql /tmp/test01.sql;
# scp -r -P 16022 /etc/letsencrypt root@192.168.0.81:/etc

主服务器授权给从服务器

//添加一个从机用户并使用从机用户可以访问主机
mysql> create user 'slave01'@'%' IDENTIFIED WITH mysql_native_password BY 'slavexxxxxx';
//授与从机所有的访问权限
mysql> grant replication slave on *.* to 'slave01'@'%';
//on *.*:对*库中的*表 (所有数据库所有的表)

//刷新授权表信息
mysql> flush privileges;
mysql> select host, user, authentication_string, plugin from mysql.user;
# service mysqld restart <<<<<<<<<

//测试,在从机尝试登录到主机
# mysql -uslave01 -p -h192.168.0.80
# mysql -uslave01 -p -P3306 -h192.168.0.80 //指定端口

编辑主服务器配置

# vi /etc/my.cnf
# vi /etc/my.cnf.d/mysql-server.cnf   <<<<<<<,
开启log-bin功能,并指定server_id为1.这里log-bin必须指定,不然的话bin-log日志会根据主机名来命名,假如主库的主机名改变了,那主从复制就失效了。
[mysqld]
server-id=1
log-bin=binlog
log-bin-index=binlog.index
relay-log=relaylog  //中转日志文件的名称(从机用,主机不需要)
expire_logs_days=2 //日志过期时间设置,默认0,即不清理

replicate-ignore-db = mysql  //不进行同步的数据库
replicate-ignore-db = information_schema //不进行同步的数据库
replicate-ignore-db = performance_schema //不进行同步的数据库
#sync-binlog = 1   //一定不能打开sync-binlog,否则会引起性能极大下降。//产生二进制日志就写入磁盘

//relay-log:一般情况下slave不会把从master接收到的binlog记录写入自己的binlog,这个参数会使slave通过SQL线程把从master接受到的binlog写进自己的binlog,但是前提是slave一定要开启自己的binlog,此参数一般用于级联复制,例如需要A复制到B,B复制到C,那么B就要开启此参数。
//relay-log:很多方面都跟log-bin差不多,区别是:从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件,然后SQL线程会读取relay-log日志的内容并应用到从服务器。
//sync-binlog = 1:默认情况下,并不是每次写入时都将binlog与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃,有可能binlog中最后的语句丢 失了。要想防止这种情况,你可以使用sync_binlog全局变量(1是最安全的值,但也是最慢的),使binlog在每N次binlog写入后与硬盘 同步。即使sync_binlog设置为1,出现崩溃时,也有可能表内容和binlog内容之间存在不一致性。

从服务器配置

# vi /etc/my.cnf
# vi /etc/my.cnf.d/mysql-server.cnf   <<<<<<<,
开启log-bin功能,并指定server_id为1.这里log-bin必须指定,不然的话bin-log日志会根据主机名来命名,假如主库的主机名改变了,那主从复制就失效了。
[mysqld]
server-id=2
log-bin=binlog
log-bin-index=binlog.index
relay-log=relaylog  //中转日志文件的名称
#同步库
replicate-do-db=book_masterdb
replicate-do-db=vjee_masterdb
#replicate-do-db=blog

#忽略库
replicate-ignore-db=mysql
replicate-ignore-db=zabbix
replicate-ignore-db=blog
replicate-ignore-db=vjee_masterdb

#忽略表
replicate-ignore-table=book_masterdb.tbl_comic_visit
replicate-ignore-table=book_masterdb.tbl_comic_visit_spider
replicate-ignore-table=book_masterdb.tbl_image_visit
replicate-ignore-table=book_masterdb.tbl_feedback_info
replicate-ignore-table=book_masterdb.tbl_verify_info

//注意不要有空格
replicate-ignore-db=mysql  //不进行同步的数据库
replicate-ignore-db=information_schema //不进行同步的数据库
replicate-ignore-db=performance_schema //不进行同步的数据库
#sync-binlog=1  //一定不能打开sync-binlog,否则会引起性能极大下降。//产生二进制日志就写入磁盘 

//relay-log:一般情况下slave不会把从master接收到的binlog记录写入自己的binlog,这个参数会使slave通过SQL线程把从master接受到的binlog写进自己的binlog,但是前提是slave一定要开启自己的binlog,此参数一般用于级联复制,例如需要A复制到B,B复制到C,那么B就要开启此参数。
//relay-log:很多方面都跟log-bin差不多,区别是:从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件,然后SQL线程会读取relay-log日志的内容并应用到从服务器。
//sync-binlog = 1:默认情况下,并不是每次写入时都将binlog与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃,有可能binlog中最后的语句丢 失了。要想防止这种情况,你可以使用sync_binlog全局变量(1是最安全的值,但也是最慢的),使binlog在每N次binlog写入后与硬盘 同步。即使sync_binlog设置为1,出现崩溃时,也有可能表内容和binlog内容之间存在不一致性。
//从服务器指定master_log_file的位置
mysql> change master to master_host='192.168.0.80',master_user='slave01',master_password='xxx',master_log_file='binlog.000001',master_log_pos=245, master_connect_retry=21600;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> change master to master_host='192.168.0.80',master_user='slave01',master_password='xxx',master_log_file='binlog.000001',master_log_pos=245;

//带端口 host
mysql> change master to master_host='ns.****50.com',master_user='slave01',master_password='slave****',master_port=*306,master_log_file='binlog.000001',master_log_pos=894, master_connect_retry=900;

//如果只需要改某部分
mysql> change master to master_log_file='binlog.000001',master_log_pos=894



//启动主从同步功能
mysql> start slave;  
//若要修改 先执行stop 每一次的任何slave操作都需要!!!!!!!!!!
mysql> stop slave; 
//修改心跳时间
mysql> change master to master_connect_retry=21600;

mysql> start slave; 
//结尾没有分号
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.80
                  Master_User: slave01
                  Master_Port: 3306
                Connect_Retry: 21600   <<<<<<<<<<<<<
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 1131254
               Relay_Log_File: relaylog.000002
                Relay_Log_Pos: 13769  <<<<<<<<<<<<<
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes   <<<<<<<<<<<<<必需是yes才成功
            Slave_SQL_Running: Yes   <<<<<<<<<<<<<必需是yes才成功
              Replicate_Do_DB: book_masterdb,vjee_masterdb
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1131254
              Relay_Log_Space: 13970
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 4d5b7ade-d127-11e9-ad84-902b3454cd10
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

主从同步常见错误

//第一种:在master上删除一条记录,而slave上找不到。
Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND
//是主从更新时丢失数据,导致主从不一致

//第二种:主键重复。在slave已经有该记录,又在master上插入了同一条记录。
Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY;

//第三种:在master上更新一条记录,而slave上找不到,丢失了数据。
Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; 

//临时解决方法
mysql> STOP SLAVE; 
mysql> SET GLOBAL sql_slave_skip_counter =1; #表示跳过一步错误,后面的数字可变 
mysql> START SLAVE; 

//利用配置参数 来躲避这个bug
# vi /etc/my.cnf.d/mysql-server.cnf
slave-skip-errors = 1032,1062,xxxx ....
点赞

发表评论

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