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