View mysql profile address
mysql --help|grep 'my.cnf' ps aux|grep mysql|grep 'my.cnf'
The configuration is divided into two parts, the master server part and the slave server part
In this test, the master and slave of the two machines are synchronized, and the environment is windows (the same as Linux)
Main server ip:192.168.1.199
Slave server ip:192.168.1.198
1) Configuration of the primary server
Find the configuration file for MySQL my.ini file
Turn on the logging function in [mysqld]
Log bin = mylog bin name can be arbitrarily selected
Server id = 199 the last three digits of the current ip
Restart the main server database service at this time
net stop mysql stop mysql service
net start mysql open mysql service
Then enter the database
Execute the following command
mysql>GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'admin';//Must be capitalized here, mysql>show master status;//View primary server log file status
+------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 439 | | | | +------------------+----------+--------------+------------------+-------------------+
The value of Position is required from the server
At this point, the configuration of the primary server is complete.
2) Configuration from server
It also needs to be modified my.ini Value in
Modify under [mysqld]
server-id=198
The log file of the slave server does not have to be opened. If it is configured for bidirectional synchronization, the log bin output needs to be configured
Restart the myql service from the server
Log in from the server and execute the following command
mysql>change master to master_host='192.168.145.199',master_user='mysync',master_password='admin',master\_log\_file='mysql-bin.000001',master\_log\_pos=439;
MySQL > show slave status \ g; / / check the status of the copy function from the server
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.199 //Primary server address Master_User: mysync //Synchronization account created by the master server Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 //Synchronized log files Read_Master_Log_Pos: 935 Relay_Log_File: PC05-relay.000003 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes //Must be yes here Slave_SQL_Running: Yes //Must be yes here Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 935 Relay_Log_Space: 1184 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 199 Master_UUID: 516cb325-a148-11e9-b3df-02004c4f4f50 Master_Info_File: C:\ProgramData\MySQL\MySQL Server 5.7\Data\master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
So far, the configuration of the server has been completed. The rest is to create the database and table for testing.
3) Testing
Master server create database
mysql>create database scrb;
mysql> create database scrb; Query OK, 1 row affected (0.01 sec)
Create table
mysql> create table user(id int(3),name varchar(15)); Query OK, 0 rows affected (0.02 sec)
Check the synchronization from the server
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | mysync | | performance_schema | | scrb | | sys | +--------------------+ 6 rows in set (0.00 sec)
View table
mysql> show tables; +----------------+ | Tables_in_scrb | +----------------+ | user | +----------------+ 1 row in set (0.00 sec)
As you can see, it has become.
The one-way master-slave synchronization configuration under this windows has been completed.
If you want to turn off master-slave synchronization, just execute the following command on the slave server
stop slave: turn off the synchronization function of slave server
start slave: enable the synchronization function of slave server
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.1.199 Master_User: mysync Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1278 Relay_Log_File: PC05-relay.000003 Relay_Log_Pos: 663 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: No //Synchronous replication has been turned off Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1278 Relay_Log_Space: 1527 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 199 Master_UUID: 516cb325-a148-11e9-b3df-02004c4f4f50 Master_Info_File: C:\ProgramData\MySQL\MySQL Server 5.7\Data\master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
It can be compared with the start state above, and it can be seen that it has been closed.