php也好、java也罢，那种语言操作mysql都离不开主从复制、读写分离、分库分表的节点。今天我们就写一下主从复制的配置。主要是把命令存下来，方便直接复制粘贴运行。
干！
首先，我们准备两台主机，不管服务器、虚拟机、docker，两个不同ip的mysql，设置好账号密码及端口。我这里有两台虚拟机Linux环境并安装了mysql：
? ? ? ? ? ? ? ? ? ? ?ip? ? ? ? ? ? ?账号? ? ? ?密码? ? ? ?端口
mysql1:? 192.168.2.251 root? ? 123456? ?3306
mysql2:? 192.168.2.252 root? ? 123456? ?3306
接下来我们分两种情况进行记录：
情况一：新建数据库实现主从复制
主机master:
1.在mysql配置文件中增加配置默认log-bin和server-id都有配置的，不要配置重复了
vim /etc/my.cnf
log-bin=mysql-bin server-id = 1 #错误日志文件 log-error=mysql-error #主从复制 #需要忽略的数据库 binlog-ignore-db=mysql #(可选)指定同步哪些数据库 binlog-do-db=boundlessz_server binlog-do-db=test
这里注意我们指定同步多个库要分别写多个binlog-do-db
2.重启mysql
[root@localhost ~]# service mysqld restart Shutting down MySQL.... SUCCESS! Starting MySQL. SUCCESS! [root@localhost ~]#
3.查看主机配置
mysql -uroot -p123456
mysql> show grants for root; +-------------------------------------------------------------+ | Grants for root@% | +-------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION | +-------------------------------------------------------------+ 1 row in set (0.00 sec)
上述查看root拥有所有权限则不执行下面命令
如果root没有拥有所有权限，我们需要给root指定权限，这里会限制相关ip进行连接
mysql> grant replication slave,reload,super ON *.* TO 'root'@'192.168.2.%' IDENTIFIED BY 'root'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
4.查看主机情况（可以先重启一下mysql）
mysql> show master status; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 2 Current database: *** NONE *** +------------------+----------+------------------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+------------------------+------------------+-------------------+ | mysql-bin.000003 | 154 | boundlessz_server,test | mysql | | +------------------+----------+------------------------+------------------+-------------------+ 1 row in set (0.01 sec) mysql> show master status; +------------------+----------+------------------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+------------------------+------------------+-------------------+ | mysql-bin.000003 | 154 | boundlessz_server,test | mysql | | +------------------+----------+------------------------+------------------+-------------------+ 1 row in set (0.00 sec)
从机slave：
1.在mysql配置文件中增加配置默认log-bin和server-id设置和master不一样的值，这里设置为2
vim /etc/my.cnf
log-bin=mysql-bin server-id = 2 #主从复制 #错误日志文件 log-error=mysql-error #主从复制 replicate-do-db = boundlessz_server replicate-do-db = test
这里注意我们指定同步多个库要分别写多个replicate-do-db
2.重启mysql
[root@localhost ~]# service mysqld restart Shutting down MySQL.... SUCCESS! Starting MySQL. SUCCESS! [root@localhost ~]#
3.查看主机配置
mysql -uroot -p123456
4.给从机指定master机器并连接
mysql> change master to master_host='192.168.2.251', master_user='root', master_password='123456', master_port=3306, master_log_file='mysql-bin.000003', master_log_pos=154; Query OK, 0 rows affected, 2 warnings (0.01 sec)
这里注意使用的参数中master_log_file和master_log_pos需要通过master执行show master status命令获取，每次修改主机配置重启后，配置都会发生变化，所以在执行上述命令时，要查看追加信息
5.启动slave，查看从机状态
mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status \G;
先停止从机
mysql> stop slave;
解决报错后的状态如下
再次启动从机
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.251
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 2091
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 638
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: boundlessz_server,test
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: 2091
Relay_Log_Space: 849
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: 0662c4cb-a265-11eb-9fea-000c294a590e
Master_Info_File: /www/server/data/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
分别均创建了一个test空数据库，
此时，我们在主库添加数据表，填充数据后从库就会相应进行同步
如果过程中由于从机宕机，从机有部分数据没有同步到，怎么进行精准同步呢？
有意思的事：
我们观察上面两张图，发现表数据由于在配置过程中，由于master多个数据库在写数据，Relay_Master_Log_File和Read_Master_Log_Pos一直都是变化的，让我们很难快速找到从库的配置该用什么数据，而且由于每次查看master状态获取的起点不一样，同步后从库数据还少了。
引用一下网上的资料：
针对这种情况，我们可以尝试使用 reset slave all;
官方的解释如下 RESET SLAVE makes the slave forget its replication position in the master's binary log. This statement is meant to be used for a clean start: It clears the master info and relay log info repositories, deletes all the relay log files, and starts a new relay log file. It also resets to 0 the replication delay specified with the MASTER_DELAY option to CHANGE MASTER TO. To use RESET SLAVE, the slave replication threads must be stopped (use STOP SLAVE if necessary). 其实，它是直接删除master.info和relay-log.info文件，并删除所有的relay log，然后重新生成一个新的relay log，即使relay log中还有SQL没有被SQL线程apply完。 但是 RESET SLAVE有个问题，它虽然删除了上述文件，但内存中的change master信息并没有删除，此时，可直接执行start slave，但因为删除了master.info和relay-log.info，它会从头开始接受主的binlog并应用。 RESET SLAVE does not change any replication connection parameters such as master host, master port, master user, or master password, which are retained in memory. This means that START SLAVE can be issued without requiring a CHANGE MASTER TO statement following RESET SLAVE. RESET SLAVE ALL 相对于RESET SLAVE，RESET SLAVE ALL还会删除内存中的连接信息，这个时候，执行start slave会报错。
也就是说，如果重置了从库，我们需要重新配置从库复制的参数，其中我们可以报master_log_file设置为001的，master_log_pos也设置从1开始
mysql> change master to master_host='192.168.2.251', master_user='root', master_password='123456', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=1;
这样我们再次重启从数据库，就能实现配置的数据库的主从全部复制
情况二：已有数据的数据库实现主从复制同步
待续。。。
