Mysql的主从复制至少是需要两个Mysql的服务,当然Mysql的服务是可以分布在不同的服务器上,也可以在一台服务器上启动多个服务。
首先确保主从服务器上的Mysql版本相同。
第一步:环境准备
linux连接工具:SecureCRT 8.1.4
linux文件管理工具:WinSCP 5.14.4
linux系统版本: CentOS 7.3 64位
mysql版本:mysql-5.7.25 安装方式参考:https://blog.csdn.net/lch520baby/article/details/89081306
第二步:配置mysql主从同步
准备两台测试的服务器(虚拟机),如上安装mysql环境,并开启mysql服务
主master : 192.168.10.242
从slave : 192.168.10.245
1、配置主库:
1)、授权给从数据库服务器
mysql>GRANT REPLICATION SLAVE ON *.* to 'rep1'@'192.168.10.245' identified by 'test123456';
mysql>FLUSH PRIVILEGES;
2)、修改主库配置文件,开启binlog,并设置server-id,每次修改配置文件后都要重启mysql服务才会生效
vim /etc/my.cnf
在该配置文件[mysqld]下面添加下面内容:
[mysqld]
log-bin=mysql-bin
server-id=1
binlog-do-db = cmdb_test
datadir=/opt/mysql/mysql-5.7.25
socket=/tmp/mysql.sock
......
server-id:master端的ID号;
log-bin:同步的日志路径及文件名,一定注意这个目录要是mysql有权限写入的;
binlog-do-db:要同步的数据库名
还可以显示 设置不同步的数据库:
binlog-ignore-db = mysql 不同步mysql库和test库
binlog-ignore-db = test
修改配置文件后,重启服务:
service mysql restart
如果启动失败,通过cat /opt/mysql/mysql-5.7.25/data/mysqld.log | tail -30 查看mysql启动失败的日志,从日志内容寻找解决方案。
3)、查看主服务器当前二进制日志名和偏移量,这个操作的目的是为了在从数据库启动后,从这个点开始进行数据的恢复
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 154 | cmdb_test | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
File是二进制日志文件名,Position 是日志开始的位置。后面从库会用到
查看big-log日志是否开启成功
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------+
| log_bin | ON |
| log_bin_basename | /opt/mysql/mysql-5.7.25/binlog |
| log_bin_index | /opt/mysql/mysql-5.7.25/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------------+
6 rows in set (0.00 sec)
可以看到从 "/etc/my.cnf" 文件中添加的 log-bin 日志已经启动
此时主库停止操作(为了保持主从数据库数据一致),等待从库连接后, 方可进行其他操作
2、配置从库
1)、理所当然也是从配置文件着手,在/etc/my.cnf 添加下面配置:
[mysqld]
server-id=2
log-bin=mysql-bin
......
server-id:服务端的ID号;
2)、修改配置文件后,重启服务:
service mysql restart
3)、配置从服务器Slave:
#数据库复制同步语句 master_log_file:对应master的file
#master_log_pos:是获master 的日志文件位置对应master 的position
mysql> change master to master_host='192.168.10.242',master_port=3306,master_user='rep1',master_password='test123456',master_log_file='mysql-bin.000002',master_log_pos=154;
mysql> start slave; //启动从服务器复制功能
4) 检查从服务器复制功能状态:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.242
Master_User: rep1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-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: 154
Relay_Log_Space: 531
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: c07a2727-59ca-11e9-b2cd-5254007ccc58
Master_Info_File: /opt/mysql/mysql-5.7.25/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
Slave_IO_Running: Yes 负责与主机的io通信
Slave_SQL_Running: Yes 负责自己的slave mysql进程
都是yes说明正常
测试:链接到数据库,创建数据库cmdb_test,创建一个测试表