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

MySQL 5.6master-slave synchronous configuration case sharing

Context of this article

Main library: Redhat 6.5x64 192.168.1.180 mysql-5.6.35

Standby library: Redhat 6.5x64 192.168.1.181 mysql-5.6.35

1. General configuration mode 1

1. Mysql master server configuration

Add the following configuration:

# vi / etc/my.cnf

[mysqld]

Log-bin = master-bin

Log-bin-index = master-bin.index

Binlog_format = mixed

Server-id = 1

Restart mysql after modifying my.cnf

# / etc/init.d/mysqld restart

[root@tmaster bin] # / etc/init.d/mysqld restart

Shutting down MySQL.. [OK]

Starting MySQL. [OK]

[root@tmaster bin] # mysql-uroot-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 1

Server version: 5.6.35-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | master-bin.000002 | 2290 | |

+-+

1 row in set (0.00 sec)

Mysql >

two。 The master server configures the synchronous replication account

Mysql > grant replication slave on *. * to 'repl'@'%' identified by' 123456'

-- refresh permissions

Mysql > flush privileges

3. Mysql slave server configuration

Description: by default, as long as the server-id is different.

-- add the following configuration:

# vi / etc/my.cnf

[mysqld]

Log-bin = slave-bin

Binlog_format = mixed

Server-id = 11

Relay-log = slave-relay-bin

Relay-log-index = slave-relay-bin.index

Configuration description: the asynchronous mysql library can realize that the master and slave libraries have different account permissions. After testing, the mysql5.6.29 is only valid in the slave library.

Other parameters:

Binlog-do-db = mydb synchronizes only one database

# replicate-ignore-db = mysql ignores the mysql library, this parameter causes a lot of unexpected synchronization problems and is still not used.

Replicate_wild_ignore_table = mysql.% ignores the mysql library

The final my.cnf from the library is as follows:

# # #

Log-bin = slave-bin

Binlog_format = mixed

Server-id = 11

Relay-log = slave-relay-bin

Relay-log-index = slave-relay-bin.index

Binlog-do-db = mydb

Replicate_wild_ignore_table = mysql.%

-- it will not take effect until it is restarted from the library mysql.

# / etc/init.d/mysqld restart

[root@tslave ~] # / etc/init.d/mysqld restart

Shutting down MySQL.. [OK]

Starting MySQL. [OK]

4. Test example

CREATE DATABASE mydb

CREATE TABLE user (

Id varchar (20) NOT NULL

Username varchar (20) NOT NULL

Password char (32) NOT NULL

PRIMARY KEY (id)

)

INSERT INTO user VALUES ('1Qing,' koumm', '123456')

INSERT INTO user VALUES ('2percent,' zhangsan', '123456')

INSERT INTO user VALUES ('3months,' lisi', '123456')

INSERT INTO user VALUES ('418,' li2si', '123456')

INSERT INTO user VALUES ('520,' abc', '123456')

INSERT INTO user VALUES ('613,' tom', '123456')

INSERT INTO user VALUES ('74th,' jk', '123456')

INSERT INTO user VALUES ('813,' xb', '123456')

5. Normal master-slave configuration process

(1) main database lock table

Mysql > flush tables with read lock

Query OK, 0 rows affected (0.00 sec)

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | master-bin.000002 | 2290 | |

+-+

Mysql > show master logs

+-+ +

| | Log_name | File_size |

+-+ +

| | master-bin.000001 | 420 |

| | master-bin.000002 | 2290 | |

+-+ +

2 rows in set (0.00 sec)

(2) backup of main database

[root@master] # mysqldump-uroot-p-B mydb > / mysql/backu/mydb.sql

Description: the-B parameter has a database-building statement.

(3) the function of the main library to unlock the table

Mysql > unlock tables

Query OK, 0 rows affected (0.00 sec)

Mysql >

6. Import the database from the library, enter the password and enter

[root@slave] # mysql-uroot-p CHANGE MASTER TO

-> MASTER_HOST='192.168.1.180'

-> MASTER_PORT=3306

-> MASTER_USER='repl'

-> MASTER_PASSWORD='123456'

-> MASTER_LOG_FILE='master-bin.000002'

-> MASTER_LOG_POS=2290

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

(2) start the slave synchronization process

Mysql > start slave

Query OK, 0 rows affected (0.01 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.180

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000002

Read_Master_Log_Pos: 2290

Relay_Log_File: slave-relay-bin.000002

Relay_Log_Pos: 284

Relay_Master_Log_File: master-bin.000002

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: mysql.%

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 2290

Relay_Log_Space: 457

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

Master_UUID: 7baedb0e-5ea0-11e8-8692-000c292185b2

Master_Info_File: / usr/local/mysql/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

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

1 row in set (0.00 sec)

ERROR:

No query specified

# check that the following two parameters are YES, which means that the slave library is running normally.

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

(3) Test synchronization

# insert a record into the main library

Mysql > use mydb

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > show tables

+-+

| | Tables_in_mydb |

+-+

| | user |

+-+

1 row in set (0.00 sec)

Mysql > select * from user

+-- +

| | id | username | password | |

+-- +

| | 1 | koumm | 123456 | |

| | 2 | zhangsan | 123456 | |

| | 3 | lisi | 123456 | |

| | 4 | li2si | 123456 | |

| | 5 | abc | 123456 | |

| | 6 | tom | 123456 | |

| | 7 | jk | 123456 | |

| | 8 | xb | 123456 | |

+-- +

8 rows in set (0.00 sec)

Mysql > insert into user values ('9pm,' Liangzhong, '654321')

Query OK, 1 row affected (0.01sec)

Mysql > commit

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from user

+-- +

| | id | username | password | |

+-- +

| | 1 | koumm | 123456 | |

| | 2 | zhangsan | 123456 | |

| | 3 | lisi | 123456 | |

| | 4 | li2si | 123456 | |

| | 5 | abc | 123456 | |

| | 6 | tom | 123456 | |

| | 7 | jk | 123456 | |

| | 8 | xb | 123456 | |

| | 9 | liang | 654321 | |

+-- +

9 rows in set (0.00 sec)

# query synchronization from the database

Mysql > use mydb

Database changed

Mysql > select * from user

+-- +

| | id | username | password | |

+-- +

| | 1 | koumm | 123456 | |

| | 2 | zhangsan | 123456 | |

| | 3 | lisi | 123456 | |

| | 4 | li2si | 123456 | |

| | 5 | abc | 123456 | |

| | 6 | tom | 123456 | |

| | 7 | jk | 123456 | |

| | 8 | xb | 123456 | |

| | 9 | liang | 654321 | |

+-- +

9 rows in set (0.00 sec)

Third, master-slave synchronization simple management

1. Stop MYSQL synchronization

STOP SLAVE IO_THREAD; # stop the IO process

STOP SLAVE SQL_THREAD; # stop the SQL process

STOP SLAVE; # stop IO and SQL processes

two。 Start MYSQL synchronization

START SLAVE IO_THREAD; # start the IO process

START SLAVE SQL_THREAD; # start the SQL process

START SLAVE; # starts the IO and SQL processes

3. Reset MYSQL synchronization

RESET SLAVE

Clear the master-slave synchronization parameter, which deletes the master.info and relay-log.info files, as well as all relay logs, and starts a new relay log.

It is applicable to reconfigure the slave library again.

4. View MYSQL synchronization status

SHOW SLAVE STATUS

5. Temporarily skip MYSQL synchronization error

To ensure that the data is consistent, this error operation is temporarily skipped as follows, possibly multiple times.

STOP SLAVE

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1

START SLAVE

# vi / etc/my.cnf

[mysqld]

# slave-skip-errors=1062,1053,1146 # Skip error of specified error no type

# slave-skip-errors=all # skip all errors

Original reference: https://www.linuxidc.com/Linux/2016-04/130729.htm

Other related materials refer to:

Implement master-slave synchronization http://www.linuxidc.com/Linux/2016-02/128100.htm between two MySQL databases

MySQL master-slave synchronization in Linux environment-- adding a new slave library http://www.linuxidc.com/Linux/2015-08/122448.htm

Using XtraBackup to build MySQL master-slave synchronous http://www.linuxidc.com/Linux/2015-08/121806.htm without downtime and table lock

MySQL master-slave synchronous configuration record http://www.linuxidc.com/Linux/2015-07/119939.htm

Master-slave synchronization configuration http://www.linuxidc.com/Linux/2016-03/129138.htm of MySQL database under Linux

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.