主从复制环境搭建
1. 服务器搭建
参照【创建虚拟机】和【安装单机板MySQL】章节，完成两台MySQL服务器的安装并配置IP地址，要求IP地址在同一网段，建议两台虚拟机部署在同一个主机上。
2. 修改主服务器MySQL配置文件
在mysql配置文件my.cnf增加如下配置：
[root@localhost etc]# vim /etc/my.cnf
server-id=248 #服务器id (主从必须不一样)
log-bin=/home/mysqllog/mysql-bin #打开logbing日志(主机需要打开)，并设置路径
binlog-do-db=tpcc #要给从机同步的库
binlog-ignore-db=mysql #不给从机同步的库(多个写多行)
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
expire_logs_days=7 #自动清理 7 天前的log文件,可根据需要修改
重启mysql服务。
[root@localhost etc]# systemctl restart mysqld.service
[root@localhost etc]# systemctl status mysqld.service
mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Thu 2019-08-01 22:49:06 EDT; 8s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 13499 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 13475 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 13501 (mysqld)
Tasks: 27
CGroup: /system.slice/mysqld.service
└─13501 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
Aug 01 22:49:05 localhost.localdomain systemd[1]: Starting MySQL Server...
Aug 01 22:49:06 localhost.localdomain systemd[1]: Started MySQL Server.
查看log bin是否设置成功。
[root@localhost etc]# mysql -uroot –p #登入mysql
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like '%log_bin%'; #查看log_bin开关及路径是否正确
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /home/mysqllog/mysql-bin |
| log_bin_index | /home/mysqllog/mysql-bin.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)
3. 创建备份账号
设置备份账号信息。
mysql> grant replication slave on *.* to 'backup'@'%' identified by '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> grant replication slave on *.* to 'backup'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
关闭主数据库的读取锁定。
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
查看master状态。
mysql> show master status \G;
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 439
Binlog_Do_DB: tpcc,tpcc500,tpcc1000
Binlog_Ignore_DB: mysql,information_schema,performance_schema,sys
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
4. 修改从服务器MySQL配置文件
在mysql配置文件my.cnf增加如下配置。
[root@localhost etc]# vim /etc/my.cnf
server-id=249 #服务器id (主从必须不一样)
重启mysql服务。
[root@localhost etc]# systemctl restart mysqld.service
修改连接主服务器信息。
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to
-> master_host='172.20.172.248', #master的ip
-> master_user='backup', #备份用户名
-> master_password='123456', #密码
-> master_log_file='mysql-bin.000002', #要与master的参数一致
-> master_log_pos=439; #要与master的参数一致
Query OK, 0 rows affected, 2 warnings (0.09 sec)
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: 172.20.172.248
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 439
Relay_Log_File: localhost-relay-bin.000003
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes #这两个状态必须要是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: 439
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: 248
Master_UUID: 521ad300-9fcb-11e9-b4f3-0cda411d6645
Master_Info_File: /home/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
|
|
如果是通过mysql虚拟机模块创建的主从服务器，里面的uuid一直会导致主从连接失败，Slave_IO_Running状态为No。解决办法是修改其中一条主机的uuid： 找到data文件夹下的auto.cnf文件，修改里面的uuid值，保证各个db的uuid不一样，重启mysql服务即可。 [root@localhost mysql]# vi auto.cnf [auto] server-uuid=521ad300-9fcb-11e9-b4f3-0cda411d6249 "auto.cnf" 2L, 56C written [root@localhost mysql]# systemctl restart mysqld.service |
