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