Learn and progress together

Master slave database configuration

1. Operation steps

1) Enable binary log of master

2) Enable binary log of slave

3) Point the slave to the master

4) Start copying

2. Open the master binary log

1) Edit mysql configuration file

1 [root@VM_0_10_centos ~]# vi /etc/my.cnf

2) Add binary log configuration and enable binary (MySQL bin is only the binary log name, which can be specified by yourself)

1 server-id=1    	#The id must be specified and is a unique id (the master database has a higher priority than the slave database)
2 log-bin=mysql-bin  =#Open binary log

3) Authorization

Login database

You need to configure a user / password permission for the slave database

1 mysql> grant replication slave on *.* to 'root'@'slave database ip' identified by 'password';

Allow a user with an ip address to copy all databases and tables in the current database with a password

Refresh permissions are required after configuration

1 mysql> flush privileges;

To modify the configuration file above, you need to restart the service

1 [root@VM_0_10_centos ~]# systemctl restart mysqld

4) View the status of the master

Login database

1 mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      154 | school       | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+

File: is the name of the log file

position: the location where the log is located and from which to copy

3. Enable the binary log of the slave

Log in to the slave server

1) Configure my CNF profile

1 [root@VM_0_16_centos ~]# vi /etc/my.cnf

2) Add slave binary log configuration and enable binary (MySQL bin is only the binary log name, which can be specified by yourself)

1 server-id=2
2 log-bin=mysql-bin

Note: each server must be assigned a unique server ID

After modifying the configuration service, restart the service

1 [root@VM_0_16_centos ~]# systemctl restart mysqld

3) Configure slave to point to master

Login database

1 mysql> change master to
2     -> master_host='master database ip',
3     -> master_user='master Authorized account number',
4     -> master_password='Authorization password',
5     -> master_log_file='master log file(mysql-bin.000001)',
6     -> master_log_pos=master Log location(154);

#Case list
mysql> change master to master_host='192.168.237.102',
    -> master_user='root',
    -> master_password='12345678',
    -> master_log_file='mysql-bin.000004',
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

The log file name of the master can be viewed in the master database using show master status. It is best to use before each authorization

Show master status view master status_ log_ File and master_log_pos parameter.

matters needing attention

mysql> change master to master_host='192.168.237.102',
    -> master_user='root',
    -> master_password='12345678',
    -> master_log_file='mysql-bin.000004',
    -> master_log_pos=154;
ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.

If the above error occurs, you need to execute the following command before executing change master to... Command.

mysql> stop slave;

4. Enable master-slave copy

Execute on the slave server

1 mysql> start slave;

View the running status of the slave

1 mysql> show slave status\G;

​ *: \ G indicates branch display

You can see the following

mysql> show slave status\G;

#main parameter 
*************************** 1. row ***************************
            Slave_IO_State: Waiting for master to send event 
            Master_Log_File: mysql-bin.000001
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
1 row in set (0.00 sec)

ERROR:
No query specified

If you can view these two, yes is successful

1 Slave_IO_Running: Yes     #Indicates that the log reading thread of the slave is on
2 Slave_SQL_Running: Yes    #Indicates that the SQL execution thread is on

5. Test master-slave replication

Create a table in the master database

mysql> create table `dog`(
    -> id int(11),
    -> name varchar(20))engine=innodb;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into dog(id,name)values(1,'zhang');
Query OK, 1 row affected (0.02 sec)

Login view from database

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| dog              |
| score            |
| student          |
| test_innodb_lock |
+------------------+
4 rows in set (0.01 sec)

mysql> select * from dog;
Empty set (0.00 sec)

mysql> select * from dog;
+------+-------+
| id   | name  |
+------+-------+
|    1 | zhang |
+------+-------+
1 row in set (0.00 sec)

If out of sync occurs, refer to the website: https://blog.csdn.net/heng_ji/article/details/51013710
Reference source: https://www.cnblogs.com/HeiDi-BoKe/p/11390490.html