MySQL master-Slave Introduction, preparation, configuration master, configuration slave, test master-Slave synchronization

MySQL Master-Slave introduction

Configure the Primary

MySQL after installation modify the MY.CNF configuration file, modify the MY.CNF, add server-id=130 and Log_bin=dashuo1

[[email protected] ~]# vim /etc/my.cnf

After modifying the configuration file, start or restart the Mysqld service

[[email protected] ~]# /etc/init.d/mysqld restart

Several files appear under/data/mysql when reboot is complete

Backup and restore the MySQL database's Zrlog library to the Hanshuo library as test data

[[email protected] mysql]# mysqldump -uroot -phanshuo1 zrlog > /tmp/zrlog.sql     ##备份db1库

[[email protected] mysql]# mysql -uroot -phanshuo1 -e "create database hanshuo"   ##创建hanshuo库

[[email protected] mysql]# mysql -uroot -phanshuo1 hanshuo < /tmp/zrlog.sql   ##把备份zrlog的数据库恢复成hanshuo数据库

Enter database create user, use as master-slave synchronization data

[[email protected] mysql]# mysql -uroot -phanshuo1    ##进入数据库

mysql> grant replication slave on *.* to ‘repl‘@‘192.168.63.104‘ identified by ‘www692517111‘;     ##创建用户

The purpose of the lock table is not to keep writing

mysql> flush tables with read lock;

mysql> show master status;

To test the backup of all libraries, use it as a synchronization

Configuration from

Added Server-id from the MySQL configuration file installed above

[[email protected] ~]# vim /etc/my.cnf

After modifying the configuration file, start or restart the Mysqld service

[[email protected] ~]# /etc/init.d/mysqld restart

Sync the Lord Aming library to the top
You can create the Aming library first, then copy the/tmp/mysql.sql of the Lord to the top, and then import the Aming library

[[email protected] ~]# scp 192.168.63.100:/tmp/*.sql /tmp/

Create a library

mysql> create database hanshuomysql> create database db1;mysql> create database zrlog;mysql> create database mysql2

Recovering a Database

Keep the master-slave database consistent

Implement Master-Slave

Log in from the database

[[email protected] ~]# mysql -uroot -phanshuo1

mysql> stop slave;

Master-Slave synchronization very important statement

mysql> change master to master_host=‘192.168.63.100‘, master_user=‘repl‘, masster_password=‘www692517111‘, master_log_file=‘dashuo1.000001‘, master_log_poos=16821;

mysql> start slave;

Determine if master/slave is configured successfully

* * Log on from the MySQL execution show slave status\g

To the Lord to restore operations

See if master-slave synchronization is working

Test Master-Slave synchronization

MySQL master-Slave Introduction, preparation, configuration master, configuration slave, test master-Slave synchronization