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

Mysql master-slave implementation

1. Configure the service profile of the master-slave node

1.1.Configuring master nodes:

[mysqld]

Binlog_format=mixed

Log-bin=master-bin

Server-id=1

Port=3306

Datadir=/mydata/data

Socket=/tmp/mysql.sock

1.2.Configuring slave nodes:

[mysqld]

Binlog-format=mixed

Log-bin=mysql-bin.log

Server-id=2

Relay-log=/mydata/relaylogs/relay-bin (need to create / mydata/relaylogs/ after chown-R mysql.mysql / mydata/relaylogs/)

Port=3306

Datadir=/mydata/data

Socket=/tmp/mysql.sock

2. Create a replication user

Lord:

Mysql > GRANT REPLICATION SLAVE ON *. * TO slave@192.168.4.% IDENTIFIED BY '12345'

Mysql > flush privileges

3. Provide the initial dataset for the standby node

Lock the master table, back up the data on the master node, and restore it to the slave node; you need to use the show master status command on the master to check the binary log file name and event location for later use when starting the slave node.

Note: the initial position of the mysql-bin can be seen through the full set.

Show master status can see the current spatial location.

It can be restored through full and incremental backups.

4. In order to test the results, first create a test library on the master machine

Mysql > CREATE DATABASE test CHARACTER SET utf8 COLLATE utf8_general_ci

Query OK, 1 row affected (0.00 sec)

Mysql > use test

Database changed

Mysql > create table if not exists test (id int (10) PRIMARY KEY AUTO_INCREMENT,name varchar (50) NOT NULL)

Query OK, 0 rows affected (0.02 sec)

Mysql > insert into test.test values (1, "liudehua"), (2, "zhangxueyou")

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

Mysql > select * from test.test

+-+ +

| | id | name |

+-+ +

| | 1 | liudehua |

| | 2 | zhangxueyou |

+-+ +

2 rows in set (0.00 sec)

5. Export more data from the master database than the slave database, and then import it into the slave database. Ensure that the data of both parties are consistent before the implementation of the synchronization environment.

Lock the database before exporting it

Mysql > flush tables with read lock

# mysqldump-uroot-p12345 test > / opt/test.sql

Then upload the exported test.sql file to the slave machine

Scp test.sql root@192.168.4.204:/root/

6. Start the replication thread from the node

Record the mysq-bin file and location, which can be synchronized from the following point

7. Import the data from master into the slave database.

Mysql > CREATE DATABASE test CHARACTER SET utf8 COLLATE utf8_general_ci; # first create an empty test library, otherwise the following import data will mistakenly say that the library does not exist. The character set needs to be the same as the main library

Mysql > use test

# mysql-uroot-p test

Stop slave

Mysql > CHANGE MASTER TO MASTER_HOST='192.168.4.203'

-> MASTER_USER='slave'

-> MASTER_PASSWORD='12345'

-> MASTER_LOG_FILE='master-bin.000004'

-> MASTER_LOG_POS=2327

Mysql > start slave

Mysql > show slave status\ G

9. Finally, unlock the main library and write data for synchronous verification:

Master:

Mysql > unlock tables

Mysql > insert into test.customers values (5, "liming", 55), (6, "guofucheng", 55)

Query OK, 1 row affected (0.00 sec)

Slave:

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.