Shulou(Shulou.com)06/01 Report--

1. The concept of master-slave synchronization and async in Mysql

Async: the master server returns the synchronous completion message immediately after writing the log, regardless of the status and influence of the slave server. MySQL defaults to asynchronous working mode.

Synchronization: the master server writes the data to the local disk and records the binary log, and then waits for the slave server to find that the data is sent and changes, then synchronizes the data to the slave server's log relay and saves it to the binary log and disk, and finally returns the synchronization results to the master server, thus affecting the performance.

2. Synchronization process:

The master server records the row contents in the binary log when it is possible to modify the data, while the slave service keeps requesting data from the master server, which is equivalent to the mysql client, constantly requesting port 3306 of the server. The log requested from the server to the master server is first recorded to the intermediate log of the slave server, and then read from the local thread of the server and saved to the local disk space of the slave server. In order to achieve the effect consistent with the content of the main server.

SLAVE:IO thread: request events in the binary log from the primary server

SQL thread: read events from the relay log and execute them locally

MASTER:binglog dump: sends the request event of IO thread to the other party

3. Points for attention:

1. The slave server must not write data, because the data will not be transferred to the master server.

2. The master server has and can only have one master server, and generally does not support a cluster of multiple masters.

3. Mariadb-10 supports multi-master model, that is, multi-source replication (mutil-source replication).

4. The master-slave version is the same as possible, or the slave version is lower than the master version.

5. The server-id of master and slave cannot be the same

4. Configuration process:

1. Master server:

1. Change server-id

2. Enable binary logging

3. Create an account with replication permission

2. From the server

1. Change server-id

2. Enable relay log

3. Point to the master server-use the account and password created by the master server

4. Start the replication process

5. Environment:

System: CentOS 6.5_x86_64

Master IP:192.168.10.204

From IP:192.168.10.205

MariaDB version: mariadb-10.0.15-linux-x86_64.tar.gz

one。 Create a new master-slave structure, that is, join the slave server if the master server does not have any data:

Primary server:

1. Change server-id

[root@node4 binlogs] # vim / etc/mysql/my.cnf

Server-id = 10

2. Enable binary logging

Log-bin=/data/binlogs/master-log

3. Create an account with replication permission

Mysql > GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *. * TO 'repluser'@'192.168.%.%' IDENTIFIED BY' 123456'

Query OK, 0 rows affected (0.03 sec)

Mysql > FLUSH PRIVILEGES; # refresh the table

Query OK, 0 rows affected (0.00 sec)

4. Restart the service:

[root@node4 binlogs] # / etc/init.d/mysqld restart

Shutting down MySQL.. [OK]

Starting MySQL. [OK]

From the server:

1. Change server-id:

[root@node5 ~] # vim / etc/mysql/my.cnf

Server-id = 100

2. Close the binary log of the slave server:

# log-bin=/data/binlogs/master-bin

Relay-log = / data/relaylogs/relay-logs # specify the log storage path of the slave server

3. Restart the service: [root@node5 relaylogs] # / etc/init.d/mysqld restart

Shutting down MySQL.. [OK]

Starting MySQL. [OK]

4. View the slave server status:

Mysql > show slave status\ G

* 1. Row**

Slave_IO_State: Waiting for master to sendevent

Master_Host: 192.168.10.204

Master_User: jack

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-log.000008

Read_Master_Log_Pos: 1537

Relay_Log_File: relay-bin.000004

Relay_Log_Pos: 536

Relay_Master_Log_File: master-log.000008

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

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: 1537

Relay_Log_Space: 827

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: 10

Master_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid: No

Gtid_IO_Pos:

Create a database on the master server to see if you can synchronize to the slave server:

Primary server:

Mysql > create databases slave; # create a new database

Mysql > show databases; # to see if the creation is complete

+-+

| | Database |

+-+

| | hellodb |

| | information_schema |

| | mysql |

| | performance_schema |

| | s |

| | slave |

| | test |

+-+

Mysql > USE slave; # switch the created database

Database changed

Mysql > create table T1 (id int); # create a new table

Query OK, 0 rows affected (0.07 sec)

Mysql > INSERT INTO T1 VALUES (1), (2), (3); # insert simple data into the table

Query OK, 3 rows affected (0.01sec)

Records: 3 Duplicates: 0 Warnings:

Mysql > select * from T1; # verify whether the data is written successfully

+-+

| | id |

+-+

| | 1 |

| | 2 |

| | 3 |

+-+

3 rows in set (0.00 sec)

Verify from the server:

Mysql > show databases; # to see if there is a database created by the master server

+-+

| | Database |

+-+

| | hellodb |

| | information_schema |

| | mysql |

| | performance_schema |

| | s |

| | slave |

| | test |

+-+

7 rows in set (0.07 sec)

Mysql > use slave; # switched database of the primary server

Database changed

Mysql > select * from T1; @ query whether there is information about the master server table

+-+

| | id |

+-+

| | 1 |

| | 2 |

| | 3 |

+-+

3 rows in set (0.00 sec)

Second, the master server is running and some data is added to the slave server:

In this process, the data of the master server is backed up, then copied to the database imported from the slave server to the slave server, and then the slave server can start backing up the data from the specified location of the master server.

The master server exports data:

Root@node4 binlogs] # / usr/local/mysql/bin/mysqldump-- all-databases-- flush-logs-- master-data=2-- lock-tables > / backup.sql

[root@node4 binlogs] # scp / backup.sql 192.168.10.205:/

Root@192.168.10.205's password:

Backup.sql 100% 518KB 518.0KB/s 00:00

Import data from the server:

[root@node5 relaylogs] # mysql

< /backup.sql mysql>

CHANGE MASTER TOMASTER_HOST='192.168.10.204',MASTER_USER='jack',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master-log.000009',MASTER_LOG_POS=367; # specifies the user, password, log file, and starting number of the synchronization master server

QueryOK, 0 rows affected (0.07 sec)

Mysql > START SLAVE; # start the synchronization process

QueryOK, 0 rows affected (0.00 sec)

Mysql > SHOW SLAVE STATUS\ G; # View status

* * 1. Row *

Slave_IO_State:Waiting for master to send event

Master_Host:192.168.10.204

Master_User:jack

Master_Port:3306

Connect_Retry:60

Master_Log_File:master-log.000009

Read_Master_Log_Pos:367

Relay_Log_File:relay-bin.000002

Relay_Log_Pos:536

Relay_Master_Log_File:master-log.000009

Slave_IO_Running:Yes

When Slave_SQL_Running:Yes # sees these two Yes displays, it means there is basically no problem.

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:367

Relay_Log_Space:827

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:10

Master_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid:No

Gtid_IO_Pos:

1 row inset (0.00 sec)

Verify that the database is synchronized successfully from the server:

Mysql > show databases

+-+

| | Database |

+-+

| | hellodb |

| | information_schema |

| | mysql |

| | performance_schema |

| | s |

| | slave |

| | test |

+-+

7 rows in set (0.00 sec)

Mysql > use slave

Database changed

Mysql > show tables

+-+

| | Tables_in_slave |

+-+

| | T1 |

+-+

1 row in set (0.00 sec)

Mysql > select * from T1; # has successfully synchronized the data to the master server

+-+

| | id |

+-+

| | 1 |

| | 2 |

| | 3 |

+-+

3 rows in set (0.00 sec)

Create a new database on the primary server and insert data into the previous T1 table to see if the synchronization is successful:

Primary server:

Mysql > create database Slave2

Query OK, 1 row affected (0.00 sec)

Mysql > INSERT INTO T1 VALUES (4), (5), (6)

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

Verify from the server:

Mysql > show databases

+-+

| | Database |

+-+

| | Slave2 |

| | hellodb |

| | information_schema |

| | mysql |

| | performance_schema |

| | s |

| | slave |

| | test |

+-+

8 rows in set (0.00 sec)

Mysql > use slave

Database changed

Mysql > show tables

+-+

| | Tables_in_slave |

+-+

| | T1 |

+-+

1 row in set (0.00 sec)

Mysql > select * from T1

+-+

| | id |

+-+

| | 1 |

| | 2 |

| | 3 |

| | 4 |

| | 5 |

| | 6 |

+-+

9 rows in set (0.00 sec)

At this point, the slave server has synchronized the previous data from the master server, and the new data can be synchronized.

Note: there are two problems encountered in the operation, as follows:

1. The database of the master server cannot be backed up, and an error is reported in an imperative manner:

[root@node4 binlogs] # mysqldump-all-databases-flush-logs-master-data=2-lock-tables > / Backup.sql

Mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': Unknown system variable' OPTION' (1193)

Problem analysis: to correct the error is to call the mysqldump command using the default path / usr/bin/mysqldump, but because MariaDB is the latest version of the installation, this backup tool is relatively old and some features are not supported, so you can use the backup tool in the MariaDB package.

2. The slave server has been unable to synchronize with the master server.

It is shown that the IO process connection is connecting all the time from the server:

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State:Connecting to master

Master_Host:192.168.10.204

Master_User:jack

Master_Port:3306

Connect_Retry:60

Master_Log_File:

Read_Master_Log_Pos:4

Relay_Log_File:relay-bin.000001

Relay_Log_Pos:4

Relay_Master_Log_File:

Slave_IO_Running:Connecting

Slave_SQL_Running:Yes

Check the log first to show that the account failed to access the master server, then it is judged that either there is no permission or the password is incorrect, change the password if the permission is correct, then stop the slave of the slave server, and restart slav with the new password, as shown below

50119 1:38:25 [ERROR] Slave jack@192.168.10.204:3306' O: error connecting to master 'jack@192.168.10.204:3306'-retry-time: 60 retries: 86400 message: Access denied for user' jack'@'node5.a.com' (using password: YES), Internal MariaDB error code: 1045

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.