Master-slave replication for mysql and master-slave replication for mysql

Master-slave replication for mysql and master-slave replication for mysql

Perform the following steps for master-slave replication:

Assume that the two master-slave mysql servers are deployed on the same machine on different ports.

For example, the server ip address is 58.83.223.20.

Ports: 3306 and 3307

If the two services are master and slave, the two services are both master and slave. That is, if the master operation is described in the following description, all the servers must perform corresponding operations. If you describe the slave operation, all servers must perform the corresponding operation.

1. The master and slave servers perform the following operations respectively::
1.1 consistent versions;
1.2 initialize the table and start mysq; l in the background
1.3 modify the root password;
2. Modify the master server:
My. ini
[Mysqld]
Log-bin = mysql-bin // [required] Enable binary log
Server-id = 222 // [required] Unique ID of the server. The default value is 1. Generally, the last segment of the IP address and the database port are used.
3. Modify the slave server slave:
My. ini
[Mysqld]
Log-bin = mysql-bin // [required] Enable binary log
Server-id = 226 // [required] unique server ID. The default value is 1. Generally, the last segment of the IP address is used.

4. Restart the mysql of the two servers.
/Bin/mysql restart

5. Create an account on the master server and authorize slave:
Mysql3306/bin/mysql-uroot-proot
Mysql> grant replication slave on *. * to 'mysync' @ '%' identifiedby 'mysync'; // generally, the root account is not used. "%" indicates that all clients may be connected. If the account and password are correct, the Client IP address can be used here, for example, 192.168.145.226, to enhance security.

Mysql3307/bin/mysql-uroot-proot
Mysql> grant replication slave on *. * to 'mysync' @ '%' identifiedby 'mysync'; // generally, the root account is not used. "%" indicates that all clients may be connected. If the account and password are correct, the Client IP address can be used here, for example, 192.168.145.226, to enhance security.

6. log on to mysql on the master server and query the master status. The File and Position may change in real time.

Mysql3306/bin/mysql-uroot-proot
Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
|Mysql-bin.000001 301|
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)

Mysql3307/bin/mysql-uroot-proot

Mysql> show master status;

+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
|Mysql-bin.000001 | 107|
+ ------------------ + ---------- + -------------- + ------------------ +

1 row in set (0.00 sec)
Note: Do not operate MYSQL on the master server after this step is completed to prevent the status of the master server from changing.
7. Configure Slave server Slave:

Mysql3306/bin/mysql-uroot-proot
Mysql> change master to master_host = '58. 83.234.21',Master_port = 3307,Master_user = 'mysync', master_password = 'mysync', master_log_file = 'mysql-bin.000001 ', master_log_pos = 107;

Mysql3307/bin/mysql-uroot-proot

Mysql> change master to master_host = '58. 83.234.21', Maid = 3306, Master_user = 'mysync', master_password = 'mysync', master_log_file = 'mysql-bin.000001 ', master_log_pos = 301;

// Do not disconnect. "107" and "301" have no single quotation marks.

//, Master_log_file ='Mysql-bin.000001', Master_log_pos = 107; show master status; found.

// When two mysql services are on the same machine, master_port must be specified; otherwise

If some errors occur:

ERROR 1201 (HY000): cocould not initializemaster info structure; more error messages can be found in the MySQL error log.

The following is a direct write solution:

1 mysql> resetslave; # focus on this line
2. mysql> change master to master_host = '58. 83.234.21 ', master_port = 3307, master_user = 'mysync', master_password = 'mysync', master_log_file = 'mysql-bin.000020', master_log_pos = 107; # Set it according to your own Environment

 

Resetting slave, MASTER_LOG_FILE and MASTER_LOG_POS will be cleared, so you need to reset them.
3. mysql> start slave; # It Will Be normal.
Mysql> start slave; // start the slave Server replication function

Note: changemaster to syntax description:
Changemaster tomaster_host = '2017. 168. x. x', master_port = XXX, master_user = 'xxx', master_password = 'xxx', master_log_file = 'mysql-bin.w.xxx ', master_log_pos = XXX;
Changemaster to option [, option]...
Option:
MASTER_HOST = 'host _ name'
| MASTER_USER = 'user _ name'
| MASTER_PASSWORD = 'Password'
| MASTER_PORT = port_num
| MASTER_CONNECT_RETRY = interval
| MASTER_LOG_FILE = 'master _ log_name'
| MASTER_LOG_POS = master_log_pos
| RELAY_LOG_FILE = 'relay _ log_name'
| RELAY_LOG_POS = relay_log_pos
| MASTER_SSL = {0 | 1}
| MASTER_SSL_CA = 'Ca _ file_name'
| MASTER_SSL_CAPATH = 'Ca _ directory_name'
| MASTER_SSL_CERT = 'cert _ file_name'
| MASTER_SSL_KEY = 'key _ file_name'
| MASTER_SSL_CIPHER = 'cipher _ list'
8. Check the slave server copy function status:
Mysql> show slave status \ G
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 58.83.223.20 // master server address
Master_User: myrync // authorize account name, avoid using root
Master_Port: 3306 // database port, which is unavailable in some versions
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 600 // # location where binary logs are synchronously read, greater than or equal to> = Exec_Master_Log_Pos
Relay_Log_File: ddte-relay-bin.000003
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes // This status must be YES
Slave_ SQL _Running: Yes // This status must be YES
......
Note: The Slave_IO and Slave_ SQL processes must run properly, that is, the YES state. Otherwise, all processes are in the wrong state (for example, any of them is in the wrong state ).
The master and slave servers are configured.
9. Master/Slave server test:
The master server Mysql creates a database and inserts a data entry into the table in this database:
Mysql> create database hi_db;
Query OK, 1 row affected (0.00 sec)
Mysql> use hi_db;
Database changed
Mysql> create table hi_tb (id int (3), name char (10 ));
Query OK, 0 rows affected (0.00 sec)
 
Mysql> insert into hi_tb values (001, 'bobu ');
Query OK, 1 row affected (0.00 sec)
Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Hi_db |
| Mysql |
| Test |
+ -------------------- +
4 rows in set (0.00 sec)

Query from the Mysql server:
Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Hi_db | // I'm here. You can see it.
| Mysql |
| Test |
+ -------------------- +
4 rows in set (0.00 sec)
Mysql> use hi_db
Database changed
Mysql> select * from hi_tb; // you can view the data added to the master server.
+ ------ +
| Id | name |
+ ------ +
| 1 | bobu |
+ ------ +
1 row in set (0.00 sec)