This article uses MySQL 5.5 CentOS 6.5 64 bits
I. The Role of Master-Subordinate Reproduction
1. If the master server has problems, it can switch to the slave server quickly.
2. Applications with low real-time requirements or infrequent updates can perform query operations from the slave server to reduce the access pressure of the primary server. The read and write of data are separated to achieve the effect of load.
3. Data backup operations can be performed from the slave server to avoid the impact on the primary server during backup.
2. Building master-slave replication environment
master: 192.168.6.224
slave: 192.168.6.222
1. Setting Authorized Users for Slave Servers in the Main Server
Create a user named user2 in the master server for the slave server 192.168.6.222 with a password of 123
mysql> grant all on *.* to user2@192.168.6.222 identified by "123";
Parametric interpretation:
grant:mysql authorization keyword
* *: All tables in all libraries
Check whether user authorization is successful:
mysql> show grants for user2@192.168.6.222;
Test whether using user2 on slave server can login mysql on master server
[root@localhost tmp]# mysql -uuser2 -p123 test -h192.168.6.224;
2. Open the bin-log log log of the main server and set the value of server-id.
Modify my.cnf configuration file of the primary server:
[mysqld]
#Open mysql bin-log log log
log-bin=mysql-bin
#The primary server is set to 1
server-id = 1
3. Reset the bin-log log log: MySQL > reset master;.
Check the latest bin-log log status to see if it is in the starting position: MySQL > show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | | |
+------------------+----------+--------------+------------------+
4. Backing up the main database data
Backup data
b. Update bin-log log log
Here we use mysqldump to backup the data and use the - l -F parameter to set the read lock and update the bin-log log log directly when backing up the data.
mysqldump -uroot -p111111 test -l -F > '/tmp/mysql_back/test.sql';
5. Send the backup data of the primary server to the slave server
[root@localhost tmp]# scp mysql_back/test.sql 192.168.6.222:/tmp/mysql_back/
6. Reset bin-log logs on slave servers and use backup data in slave servers
mysql> rester master;
[root@localhost tmp]# mysql -uroot -p111111 test -v -f</tmp/mysql_back/test.sql;
7. Configure my.cnf parameters in slave server
Configuration slave server-id =2 (if there are multiple slave servers, there is a unique server-id)
server-id = 2
b, # Open bin-log log log
log-bin=mysql-bin
Configuration of hosts, usernames, passwords, port numbers that need syn c hronization
#Configure hosts that need synchronization
master-host = 192.168.6.224
# The username the slave will use for authentication when connecting
# to the master - required
master-user = user2
#
# The password the slave will authenticate with when connecting to
# the master - required
master-password = 123
#
# The port the master is listening on.
# optional - defaults to 3306
master-port = 3306
d. Restart mysql for configuration files to take effect
[root@localhost tmp]# service mysqld restart
If you can't restart the mysql server by changing the mode, you can use the following method
mysql> change master to master_host="192.168.6.224", master_user="user2", master_password="123", master_port=3306, master_log_file="mysql-bin.000002",master_log_pos=107; mysql> slave start;
8. View slave status
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.6.224 Master_User: user2 Master_Port: 3306 Connect_Retry: 60 //Synchronize bin-log logs with master server every 60 seconds Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 107 Relay_Log_File: localhost-relay-bin.000002 //slave server log
Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes //These two lines are yes to indicate that the master-slave configuration was successful
Master_Log_File: Represents the name of the log file used for primary and standby synchronization on the host.
Read_Master_Log_Pos: Represents the location in the log file that was successfully synchronized last time.
If these two items do not match the values of File and Position previously seen on the primary server, they cannot be synchronized correctly.
Three, test
1. Add data to master server and view bin-log log status
mysql> insert into t1 values(13); Query OK, 1 row affected (0.02 sec) mysql> insert into t1 values(14); Query OK, 1 row affected (0.01 sec) mysql> insert into t1 values(15); Query OK, 1 row affected (0.01 sec) mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 656 | | | +------------------+----------+--------------+------------------+
2. View slave synchronization status
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.6.224 Master_User: user2 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 656 Relay_Log_File: localhost-relay-bin.000002 Relay_Log_Pos: 802 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes
Here you can see that the Postion of the primary server is equal to the Read_Master_Log_Pos value of the slave server and that the Slave_IO_Running and Slave_SQL_Running values are Yes. In this way, the master-slave configuration of mysql is successful.
4. Common commands of master-slave replication
1. start slave # Start replication threads
2. stop slave # stop replication threads
3. show slave status # View the status from the database
4. show master logs; # Check what bin-log logs are in the main database
5. Changemaster to # Dynamic Change to the Configuration of the Main Server
6. show process list; # view the running process from the database