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

What is Mysql master-slave synchronization?

Synchronize data on the master database from the library, (let other database servers automatically synchronize data on the data server currently being accessed)

The structure type of master-slave synchronization

1 master-> 1 slave (commonly used) one master server (responsible for business updates) and one slave server

1 master-> multi-slave (commonly used) one master server (responsible for business update) multiple slave servers

Master-> Slave-> one master server (responsible for business updates), one slave server (as the other master server), one slave server

Master (not used alone, usually work with third-party software to do highly available cluster or load balancing) servers each other as master and slave servers

Environmental requirements

The 1.mysql server version needs to be the same

two。 The network can be interconnected

3. Firewall and selinux need to be released mysql experimental environment can turn off selinux and firewall

4. Before configuring the master-slave synchronization structure, make sure that the slave database has the same data as the master database (if the data structure of the two servers is different and the sql that can be executed on the master server is on the slave server, if there is an error executing on the slave server, the sql thread will NO and will no longer synchronize)

Primary database server

1. User authorization

Grant replication slave on. To authorized user @ "ip address from server" identified by "password"

two。 Enable binlog Log

Vim / etc/my.cnf

[mysqld]

Do not repeat the value of server_id=0-255master-slave.

Log-bin=/ specifies the folder / log file name of the binlog log mysql service users need to have read and write permissions for the specified folder can not be specified by default under the database directory / var/lib/mysql

Binlog-format= "mixed" uses mixed mode to log (mixed mode records changes and sql commands)

3. View binlog logs in use

Show master status

From the database server

1. Verify Master Library Authorization

# mysql-h main library address-u authorized user-p authorized password

The authorization is successful if you can log in from the server command line.

two。 Specify server_id

Vim / etc/my.cnf

[mysqld]

Server_id=52

: wq

Show slave status; checks to see if it is from the library information.

Because it hasn't been specified yet, no information can be found.

3. Specify main library information

Mysql > change master to

-> master_user= "repluser", / / Master server authorized user

-> master_host= "192.168.4.51", / / master server ip address

-> master_password= "123456", / / authorization password

-> master_log_file= "A1-bin.000001", / / the binlog log being used by the master server can be viewed on the master server by show master status to see the log in use and the offset that has been recorded

-> offset recorded in the master_log_pos=452;//binlog log

Query OK, 0 rows affected, 2 warnings (0.23 sec)

4. View configuration

Show slave status\ G

1. Row

Slave_IO_State:

Master_Host: 192.168.4.51

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: A1-bin.000001

Read_Master_Log_Pos: 452

Relay_Log_File: A2-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: A1-bin.000001

Slave_IO_Running: No

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

Relay_Log_Space: 154

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

Master_UUID:

Master_Info_File: / var/lib/mysql/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)

5. Instrumental salve process

Mysql > start slave

Query OK, 0 rows affected (0.08 sec)

When you view the configuration again, the values of the following two parameters are YES

Slave_IO_Running: the YES / / IO thread records the sql command of the binlog log on the primary server (the Binlog Dump on the primary server pushes the new log record) to the local relay log file / var/lib/mysql/ hostname-relay-bin. Numbered Slave_SQL_Running: YES / / SQL thread

Verify the configuration

Create a database on the master server, and there is a database created on the master server on the slave server

Common causes of errors

I am not able to get up the thread

An error in the configuration parameter will

Firewalls

Selinux

If there is an error, the following parameters will have the reason for recording the error.

Show slave status\ G

Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error:

The SQL thread cannot get up

The database is not unified before the master-slave configuration, resulting in a conflict

Solution stop slave

First restore the database from the server to a unified database on the primary server

Restart start slave

One master and more slaves means adding another slave server.

Create a new slave server authorized user on the master server

Configure another slave database server

Master database server partial profile parameters

[mysqld]

List of binlog_do_db= library names / / allowed from synchronized libraries

List of binlog_ignore_db= library names / / not allowed from synchronized libraries

Neither parameter is written to synchronize all libraries.

Configure file parameters from the database server section

[mysqld]

List of binlog_do_db= library names / / libraries synchronized only from the master

List of binlog_ignore_db= library names / / libraries that are not synchronized from the master

Neither parameter is written to synchronize all libraries.

Log_slave_updates / / cascade replication logs to the binlog log when executing the sql command in the relay log. By default, binlog does not record the sql that executes the relay log (this parameter needs to be configured on the slave server in the middle of the master-slave structure)

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.