Detailed explanation of master-slave replication in mysql learning

This article uses mysql5.5 centos6.5 64-bit

1. The role of master-slave replication

1. If the master server If a problem occurs, you can quickly switch to the slave server.

2. Applications that have low real-time requirements or infrequent updates can perform query operations on the slave server to reduce the access pressure on the master server. Separate the reading and writing of data to achieve the load effect.

3. Data backup can be performed on the slave server to avoid the impact on the master server during the backup period.

Master-slave replication principle:

Principle analysis: The master server opens the binlog log, and the slave server will use the user granted by the master server to The binlog log generated by the master server is read locally and converted into a relaylog log, and then the relaylog log is executed.

2. Set up a master-slave replication environment

master: 192.168.6.224

slave: 192.168.6.222

1. Set the authorized user for the slave server in the master server

Create a user named user2 in the master server for the slave server 192.168.6.222, and the password is 123

mysql> ; grant all on *.* to user2@192.168.6.222 identified by "123";

Parameter explanation:

grant:mysql authorization keyword

*.*: All libraries and all tables

Check whether user authorization is successful:

mysql> show grants for user2@192.168.6.222;

Test whether user2 can log in to mysql on the master server using user2 on the slave server

[root@localhost tmp] # mysql -user2 -p123 test -h192.168.6.224;

2. Open the bin-log log of the main server and set the value of server-id.

Modify the my.cnf configuration file of the master server:

[mysqld]
#开启mysql的bin-log日志
log-bin=mysql-bin
#主服务器该值设置为1
server-id    = 1

3. Reset the bin-log log: mysql> reset master;

Check the latest bin-log log status to see if it is at the starting position: mysql> show master status;

mysql> show master status;
+------------------+----------+--------------+------------------+
| 
File
             | 
Position
 | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000
001
 |      107 |              |                  |
+------------------+----------+--------------+------------------+

4. Back up the master database data

　a. Back up data

b. Update bin-log log

Here we use the mysqldump method to back up the data and use the -l -F parameters to directly set the read lock and update the bin-log when backing up the data. Log

　mysqldump -uroot -p111111 test -l -F > '/tmp/mysql_back/test.sql';

5. Send the data backed up by the master server to the slave Server

　[root@localhost tmp]# scp mysql_back/test.sql 192.168.6.222:/tmp/mysql_back/

6. Reset the bin on the slave server -log log and use the backed up data in the slave server

　mysql> rester master;

　[root@localhost tmp]# mysql -uroot -p111111 test -v -f< /tmp/mysql_back/test.sql;

7. Configure the my.cnf parameters in the slave server

a. #Configure the slave server server-id =2 (if If there are multiple slave servers, they all have a unique server-id)
Server-id = 2

b. #Enable bin-log log
Log-bin=mysql-bin

　c. #Configure the host, user name, password, and port number that need to be synchronized

#配置需要同步的主机
 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 to make the configuration file take effect

　[ root@localhost tmp]# service mysqld restart

If you cannot restart the mysql server after changing the method, 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. Check the slave status

mysql . row  master .bin.relaybin.bin.

Master_Log_File: represents the name of the log file used for master-slave synchronization on the host,

Read_Master_Log_Pos: represents the position 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 main server, synchronization cannot be performed correctly.

3. Test

1. Add data to the master server and check the 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. Check the 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 master server and the Read_Master_Log_Pos value of the slave server are equal and the values ​​of Slave_IO_Running and Slave_SQL_Running are both Yes. In this way, the master-slave configuration of mysql is successful.

4. Common commands for master-slave replication

1. start slave #Start the replication thread

2.stop slave #Stop the replication thread

3. show slave status #View slave database status

4.show master logs;#Check the bin-log logs of the master database

5.change master to #Dynamic change to Main server configuration

6, show processlist;#View the running process of the slave database

The above is the detailed content of Detailed explanation of master-slave replication in mysql learning. For more information, please follow other related articles on the PHP Chinese website!