MySQL + +: Liunx - MySQL master-slave replication

Objective: build two MySQL servers, one as the master server and one as the slave server to realize master-slave replication

Environment: virtual machines

Main database: 192.168.211.101

From database: 192.168.211.102

For MySQL installation, please refer to: go for a walk

1) : main library configuration steps

1. Configure master database: authorize to slave database server

mysql>GRANT REPLICATION SLAVE ON *.* to 'rep1'@'192.168.211.102' identified by 'root';
mysql>FLUSH PRIVILEGES;

2. Modify the configuration file of the main database, open binlog, and set the server ID. the mysql service will not take effect until you restart it after modifying the configuration file

[mysqld]
# Synchronization log path and file name. Be sure to note that if mysql has permission to write to this directory
log-bin=mysql-bin
# ID number of the master side
server-id=1
# To synchronize mstest data base,To synchronize multiple databases, add more replicate-db-db=Database name 
binlog-do-db=verymemory
# Databases to ignore
#binlog-ignore-db=mysql

After modifying the configuration: restart the MySQL service. If the startup fails, use cat / var / log/ mysqld.log |Tail - 30 view the log of MySQL startup failure and find solutions from the log content.

3. Check the current binary log name and offset of the primary server. The purpose of this operation is to recover data from this point after starting from the database

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      120 | verymemory   |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

The primary server is configured.

2) : configure steps from library

1. Of course, we should start from the configuration file in / etc/my.cnf Add the following configuration:

[mysqld]
server-id=2
master-host=192.168.211.101
master-user=rep1
master-password=root
master-port=3306
replicate-do-db=verymemory
......

Error in restart: mysqld: unknown variable 'master host=

This indicates that mysql does not recognize these variables, because the master-slave replication of mysql version 5.5 + does not support these variables, which need to be set on the slave database with the command:

CHANGE MASTER TO MASTER_HOST='192.168.211.101',MASTER_PORT=3306,MASTER_USER='rep1',MASTER_PASSWORD='root',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=120;

Note possible errors: error 1794 (HY000): slave is not configured or failed to initialize properly. You must at least set -- server ID to enable either a master or a slave. Additional error messages can be found in the MySQL error log

------------------------  ERROR  START  ------------------------

1. If the versions are the same, do the following

vim /etc/my.cnf

#Add the following configuration
log-bin=mysql-bin
server-id=2

2. If the versions are different, do the following

Reason: use innobackupex backup from version 5.6.25, apply recovery in version 5.7.15, ibd system table needs to be rebuilt

Solution steps:

1) : ibd table for drop backup

#Log in to the database
mysql -uroot -p
#Using mysql database
use mysql;
#delete
drop table slave_master_info;
drop table slave_relay_log_info;
drop table slave_worker_info;
drop table innodb_index_stats;
drop table innodb_table_stats;
#reconstruction
source /app/mysql-5.7.25/share/mysql_system_tables.sql
#Exit mysql
quit
#Restart mysql
/etc/init.d/mysqld restart
//Or service mysqld restart
//Note: you can modify it according to your mysql path

At this point, the problem is solved. Log in to the database and change the master to again!

--------------- ERROR  END  -----------------

2. Start the slave process

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

3. Check the status of slave. If the following two values are YES, the configuration is correct:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Slave is waiting for master to update data... Waitin for master to send event...

So far, if the master database needs to be operated, it will be synchronized to the slave database.

3) : synchronize the existing data in the master database to the slave database [select whether to operate the following steps according to personal situation]

Master database operation:

1. Stop the data update operation of the main database

mysql>flush tables with read lock;

2. Open a new terminal to generate a backup of the main database (export database)

./mysqldump -h127.0.0.1 -uroot -proot verymemory > /opt/mysql/install/backups/verymemory.sql

3. Transfer backup files to slave

scp /opt/mysql/install/backups/verymemory.sql root@192.168.211.102:/opt/mysql/backups

4. Master library unlocking

mysql>unlock tables;

From library operation:

1. Stop slave

mysql>slave stop;

2. New database cmdb

mysql> create database verymemory default charset utf8;

3. Import data

[root@ops-dev ~]# mysql -uroot -ptest123 verymemory</opt/mysql/install/backups/verymemory.sql

4. View the existing database and data from the library

 

At this time, the data of the master-slave database is completely consistent. If you add, delete or modify the master database, the slave database will automatically synchronize.

 

More MySQL master-slave replication mechanisms will be updated in the next article

Tags: MySQL Database SQL vim

Posted on Wed, 20 May 2020 01:01:25 -0400 by void_function