Mysql database master-slave Replication

1 )*.LoginMysqlDatabase master server and set authorized users for slave server.

# Mysql-uroot-p123456 test

Mysql> grantall (slave) on *. * to user1_zc@192.168.10.2identified by "456 ";

Or

Mysql> grantreplication (slave) on *. * user1_zc@192.168.10.2identified by "pass ";

Mysql> showgrants for user1_zc@192.168.10.2; // check whether user authorization is successful

Ormysql> selectuser, host, password from mysql. user;

2 )*.LoginMysqlDatabase master server clearingBin-logLog records.

Mysql> resetmaster;

Mysql> showmaster status;

3 )*.ModifyMysqlConfiguration file of the database master server/Etc/my. cnf, Account OpeningBin-log,And setServer-idAnd restart the service..

# Vi/etc/my. cnf is enabled by default)

Log-bin = mysql-bin

Server-id = 1 (server-id can be set once)

# Servicemysqld restart or/usr/local/mysql/bin/mysqld_safe-user = mysql &

4 )*.BackupMysqlDatabase master server database)

#/Usr/local/mysql/bin/mysqldump-uroot-p123456 test-l-F>/tmp/test. SQL

5 )*.Restore the backed up database to the slave server.

# Scp/tmp/test. SQL root@192.168.10.2:/tmp/

6 )*.FromMysqlRecover data on the database server(Library)

#/Usr/local/mysql/bin/mysql-uroot-p123456 test </tmp/test. SQL

Mysql> showtables;

Mysql> select * from t1; // check whether the Master/Slave Data reaches

7 )*.ClearBin-logLogs(Original Master/Slave DatabasesBin-logLog consistency)

Mysql> resetmaster;

Mysql> showmaster status;

8 ).*Modify the configuration file of the slave Database ServerMy. cnfAnd restart the service..

Log-bin = mysql-bin

Server-id = 2 (server-id can be set once)

Master-host = 192.168.10.1

Master-user = user‑zc

Masters-password = 456

Master-port = 3306

# Servicemysqld restart or/usr/local/mysql/bin/mysqld_safe-user = mysql &

9 ).Slave TestUser‑zcCan a user log on to the master server from the slave server?

#/Usr/local/mysql/bin/mysql-uuser1_zc-p456-h192.168.10.2 test

10 ).Test data insertion on the master server and check whether the data is consistent on the slave server.

This article is from the "mhf_zzjc" blog, please be sure to keep this http://1meir3a.blog.51cto.com/6176787/1298417