step by step configures the specific method of mysql replication

  • 2020-06-01 11:09:00
  • OfStack

Full library replication is adopted to facilitate the management of mysql 5.5 + rhel5.8
10.4.11.12 master
10.4.11.13 slave
- Master end setting
1, create a copy account, need to copy and slave permissions
mysql > GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'10.4.11.12 IDENTIFIED BY 'mysql;
Query OK, 0 rows affected (0.00 sec)
2. Modify parameters and restart to take effect
#skip-networking
server-id = 12
# Uncomment the following if you want to log updates
log-bin=mysql-bin
Restart mysql
service mysql restart

--slave terminal Settings
3. The parameter setting of slave library also needs to be restarted to take effect
vi /etc/my.cnf
#skip-networking
server-id = 13
# Uncomment the following if you want to log updates
log-bin=mysql-relay-bin

4, master exports data to the slave library
If the database is using the MyISAM table type, you can do this:

shell > mysqldump --all-databases --master-data=1 > data.sql
If the database is using the InnoDB table type, single-transcation should be used:

shell > mysqldump --all-databases --single-transaction --master-data=1 > data.sql

The slave side imports data
mysql < data.sql


5. View the configuration of master
mysql > show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

6. Start replication. The slave side runs the following command


-- Configure the direction of replication 
CHANGE MASTER TO MASTER_HOST='10.4.11.12',
 MASTER_USER='repl',
 MASTER_PASSWORD='mysql',
 MASTER_LOG_FILE='mysql-bin.000001',
 MASTER_LOG_POS=0;

-- Begin to copy 
mysql> start slave ;
Query OK, 0 rows affected (0.00 sec)

7. To view the status of the replication, notice the values of columns slave_io_state, slave_io_Running, and slave_sql_running3

mysql > show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.251.11
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1278
Relay_Log_File: linux2-relay-bin.000002
Relay_Log_Pos: 503
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
... .

8. Test the replication
Create a new table at Master
Slave checks to see if the copy is complete


Related articles: