配置主库
- 主库配置文件修改。
主库安装在Windows系统上,修改my.ini文件。
[wampmysqld64]
log-bin=mysql-bin
binlog-do-db = test2
[mysqld]
default_authentication_plugin=mysql_native_password
port = 3306
log-bin=mysql-bin
binlog-do-db = test2
server-id=1
我的MySQL装在wamp中,所以多写了两句。主要添加log-bin,开启二进制日志文件。
binlog-do-db = test2是制定复制这个database,其他的database不复制。
server-id=1默认,主从的server-id不能相同。
- 重启mysql,添加用户和权限。
mysql> grant replication slave, replication client on *.* to 'rpl'@'192.168.52.101' identified by '123456';
Query OK, 0 rows affected
192.168.52.101是从库的地址,rpl是创建的用户,密码为123456.
mysql> show global variables like '%server%';
+---------------------------------+--------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------+
| character_set_server | latin1 |
| collation_server | latin1_swedish_ci |
| innodb_ft_server_stopword_table | |
| server_id | 1 |
| server_id_bits | 32 |
| server_uuid | f0a38636-006e-11ea-95db-8c16454b8c88 |
+---------------------------------+--------------------------------------+
6 rows in set
可以查看主库的server-id。
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 878 | test2 | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set
查看master状态,记住file和position,等会配置从库使用。从服务器就是要指定这个文件master-bin.000001 (随着mysql增删修改日志文件名字也有可能改变) 和 878 (position点 主服务器增删修改都会改变)所以现在开始就不要再在主服务器创表干啥的了~
mysql> flush privileges;
刷新权限,okk~
- 从数据库配置。
修改从数据库的my.cnf文件,将server-id=2即可,从数据库的my.cnf文件放在/etc/中。
server-id必须唯一~ - 重启从数据库
重启失败,报错ERROR! The server quit without updating PID file (/usr/local/mysql-5.7.26/data/localhost.localdomain.pid).
原因找到,配置文件的pid-file文件位置写错了,没有这个文件夹。
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
重启之后进入从服务器的mysql。
CHANGE MASTER TO MASTER_HOST='192.168.52.1',
MASTER_USER='rpl',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=878;
查看从服务器状态。主要看这两句
Slave_IO_Running: No
Slave_SQL_Running: No
主从没有连接上所以io是no,没开启从服务器所以sql是no的状态。
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.52.1
Master_User: rpl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1192
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
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: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1192
Relay_Log_Space: 154
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1045
Last_IO_Error: error connecting to master 'rpl@172.31.107.30:3306' - retry-time: 60 retries: 13
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /usr/local/mysql-5.7.26/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 201118 21:51:11
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)
开启从服务器
mysql> start slave;
再次查看状态,两条都变成yes。
- 在主库的相应数据库test2上对数据进行增删改查,从库也对应改变,测试成功,okk~~
- 突然因为一些特殊字符或者语句报错,从库停止同步了,查看从库状态,发现
Slave_SQL_Running: No
处理方法:
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.03 sec)
成功继续同步=0=