Configure MySQL primary-secondary replication
Deploy MySQL servers
Deploy two MySQL servers on VMs as described in "Create VMs" and "Standalone MySQL installation procedure," and assign the MySQL servers IP addresses from the same subnet. As a best practice, deploy the VMs on the same host.
Modify the configuration files of the primary MySQL server
Add the following settings to the my.cnf file.
[root@localhost etc]# vim /etc/my.cnf
server-id=248 #Server ID (The IDs of the primary and secondary MySQL servers must be different.)
log-bin=/home/mysqllog/mysql-bin #Enable binary logging on the primary MySQL server and set the log path.
binlog-do-db=tpcc #Specify the database to synchronize with the secondary MySQL server.
binlog-ignore-db=mysql #Specify the databases to ignore in synchronization.
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
expire_logs_days=7 #Enable the system to automatically clear the logs older than sever days.
Restart the MySQL service.
[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.
Check the state of binary logging.
[root@localhost etc]# mysql -uroot –p #Log in to 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%'; #Check the status and path of binary logging.
+---------------------------------+--------------------------------+
| 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)
Create a backup account
Create a backup account.
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)
Release the read lock of the primary database.
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
View the status of the primary MySQL server.
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
Modify the configuration files of the secondary MySQL server
Add the following settings to the my.cnf file.
[root@localhost etc]# vim /etc/my.cnf
server-id=249 # Server ID (The IDs of the primary and secondary MySQL servers must be different.)
Restart the MySQL service.
[root@localhost etc]# systemctl restart mysqld.service
Modify the connection to the primary MySQL server.
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', # Backup username.
-> master_password='123456', # Password.
-> master_log_file='mysql-bin.000002', # Must be the same as the one on the master.
-> master_log_pos=439; # Must be the same as the one on the master.
Query OK, 0 rows affected, 2 warnings (0.09 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
View the status of the secondary MySQL server.
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 # Make sure this state and the Slave_SQL_Running state are 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
|
|
If you create the primary and secondary MySQL servers through the MySQL VM module, UUID settings will cause connection failure and the Slave_IO_Running state is always No. To resolve this problem, modify the UUID of one of the servers. Open the auto.cnf file in the data folder and set unique UUIDs for databases, and then restart the MySQL service. [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 |
