mysql 主从配置
1、mysql的安装：https://blog.csdn.net/Aykl119/article/details/122223582
2、主从配置步骤：
a、配置主机
增加如下
server_id = 1 // 唯一标识
log-bin = master-a-bin // 日志文件名称
binlog-format=ROW // 日志格式
binlog_do_db=TEST // 需要同步的数据库

对从服务器授权
grant replication slave on . to ‘root’@‘192.168.131.%’ identified by ‘root’;
flush privileges;
或者需要给全部的权限
grant all privileges on . to ‘root’@‘192.168.131.%’ identified by ‘root’ with grant option;
b、从服务配置
server_id = 2 // 唯一标识
log-bin = master-a-bin // 日志文件名称
binlog-format=ROW // 日志格式

完成后重启服务：
service mysqld start/stop/restart
或：systemctl mysqld restart
查看服务状态：service mysqld status

c、查看主服务器的状态
show master status
mysql> show master status
-> ;
±--------------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±--------------------±---------±-------------±-----------------±------------------+
| master-a-bin.000002 | 120 | TEST | | |
±--------------------±---------±-------------±-----------------±------------------+
1 row in set (0.01 sec)

d、配置从服务器：
CHANGE MASTER TO
MASTER_HOST=‘192.168.131.141’, //主服务器
MASTER_PORT=3306,
MASTER_USER=‘root’,
MASTER_PASSWORD=‘root’,
MASTER_LOG_FILE=‘master-a-bin.000002’, // master
MASTER_LOG_POS=120;

e、从服务器配置好后，重启mysql服务
设置为slave
start slave;
// 查看状态
show slave status\G;

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.131.141
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-a-bin.000002
Read_Master_Log_Pos: 1545
Relay_Log_File: linux001-relay-bin.000010
Relay_Log_Pos: 320
Relay_Master_Log_File: master-a-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 1545
Relay_Log_Space: 696
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: bc1e2413-b8d5-11ec-a822-000c299e074d
Master_Info_File: /var/lib/mysql/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:
1 row in set (0.00 sec)

ERROR:
No query specified

f、测试
主服务增加数据：
insert into Employees values(5,35,‘cv’,‘vvvvc’);
从服务器查看：
mysql> select * from Employees;
±----±----±-------±-------+
| id | age | first | last |
±----±----±-------±-------+
| 100 | 18 | Zara | Ali |
| 101 | 25 | Mahnaz | Fatma |
| 102 | 30 | Zaid | Khan |
| 103 | 28 | Sumit | Mittal |
| 105 | 33 | xjn | adas |
| 3 | 30 | tttt | xxx |
| 5 | 35 | cv | vvvvc |
±----±----±-------±-------+
7 rows in set (0.00 sec)

3、问题
a、mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.131.141
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-a-bin.000002
Read_Master_Log_Pos: 1545
Relay_Log_File: linux001-relay-bin.000005
Relay_Log_Pos: 320
Relay_Master_Log_File: master-a-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1805
Last_Error: Error ‘Column count of mysql.user is wrong. Expected 45, found 43. The table is probably corrupted’ on query. Default database: ‘’. Query: ‘GRANT REPLICATION SLAVE ON . TO ‘root’@‘192.168.131.%’ IDENTIFIED BY PASSWORD ‘*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B’’

解决方法：
[root@linux001 devlops]# /usr/bin/mysql_upgrade -uroot -p -S /var/lib/mysql/mysql.sock;

b、mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.131.141
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-a-bin.000002
Read_Master_Log_Pos: 1545
Relay_Log_File: linux001-relay-bin.000007
Relay_Log_Pos: 874
Relay_Master_Log_File: master-a-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Could not execute Delete_rows event on table TEST.Employees; Can’t find record in ‘Employees’, Eor_code: 1032; handler error HA_ERR_END_OF_FILE; the event’s master log master-a-bin.000002, end_log_pos 1514

解决方法：
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> set global sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)