How to set the master-slave database of Linux MySQL

1. Single master and single slave

1. the versions of the master and slave databases must be consistent.

2. on the master server, set an account for the SLAVE database and use replication slave to grant permissions.

Mysql> grant replication slave on *. * to rep @ '192. 192.% 'identified by '123 ';

Mysql> select user, host from mysql. user; --- check whether the command takes effect.

3. modify the configuration file my. cnf of the primary database.

[Mysqld]

Log-bin = mysql3306-bin

Server-id = 1

4. The current binary log name and offset of the master server. This operation aims to restore data from this point after the database is started.

Mysql> show master status \ G;

* *************************** 1. row ***************************

File: mysql3306-bin.000002

Position: 440

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

5. you can stop the update operation of the primary data and generate a backup of the primary database. You can use mysqldump to migrate the primary database to the slave database. Note that read lock is performed on the primary database before data is exported, to ensure data consistency

Mysql> flush tables with read lock; --- lock the database

Query OK, 0 rows affected (0.19 sec)

Mysqldump-u root-p-S/data/3306/mysql. sock-A-B>/home/oldboy/mysql. SQL --- Note-A indicates backing up all databases-B Indicates adding use DB and drop (the original database will be overwritten during Database Export)

 

Mysql> unlock tables;

 

Query OK, 0 rows affected (0.28 sec)

 

6. Copy the backup of the master database to the slave database for import.

7. modify my. cnf from the database.

[Mysqld]

 

Relay-log = slave-relay.bin

Server-id = 10

 

8.Users used for replication,Master Database ServerIp,Port and the file and location where the replication log is started.

 

[Root @ localhost 3306] # cat | mysql-uroot-p '000000'-S/data/123456/mysql. sock <EOF

> CHANGE MASTER

> MASTER_HOST = '1970. 168.0.32 ',

> MASTER_PORT = 3306,

> MASTER_USER = 'rep ',

> MASTER_PASSWORD = '000000 ',

> MASTER_LOG_FILE = 'mysql3306-bin.000002 ',

> MASTER_LOG_POS = 440;

> EOF

 

9. Restart slave database and verify.

 

[Root @ localhost 3306] # mysql-uroot-p '000000'-S/data/123456/mysql. sock-e "start slave ;"

[Root @ localhost 3306] # mysql-uroot-p '000000'-S/data/123456/mysql. sock-e "show slave status \ G" | grep-I yes

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

1 2 Next Page