MySQL Master-slave replication

1, MySQL master-slave replication scheme with Scp/rsync and other commands such as file-level replication is similar to the remote transmission of data. It's just that MySQL comes with this feature without the use of third-party software.

2, its replication is not copied from the disk, but through the logical binlog log to fetch the SQL statement re-applied to the MySQL database.

3, to achieve master-slave replication, master needs to open my.cnf in the Binlog log function. The specific action is to add the following parameters:

[Mysqld]

Log-bin =/data/3306/mysql-bin--from the database can not be set. Avoid impacting performance. Of course it's okay to set up.

Note that the statement needs to be placed under the [Mysqld] module.

4. The Server-id of all DB instances used for master-slave replication cannot be the same. This must be noted.

Note: The parameters in the my.cnf are not repeatable. Need to restart database after modification

5. Create an account for replication: Log in to the main database and execute the following statement:

Grant Replication Slave on * * to ' rep ' @ ' 10.108.199.% ' identified by ' redhat '; you can see that only replication permissions are granted. Only 10.108.199 network segments are allowed to be accessed by Rep.

Select User,host from mysql.user;--to view the results.

6. Back up the database statement:

Mysqldump-uroot-p ' Redhat '-s/data/3306/mysql.sock--events-a-B |gzip >/data/backup/mysql_bak.$ (Date +%F). sql.gz

Then unzip.

Then restore the extracted files to another instance.

Mysql-uroot-p ' Redhat '-s/data/3308/mysql.sock < Mysql_bak.2017-06-19.sql

Login from the database:

Change MASTER to master_host= ' 10.0.0.7 ', master_port=3306, master_user= ' rep ', master_password= ' Redhat ', Master_log_ File= ' mysql-bin.000001 ', master_log_pos=1878;--corresponding parameters need to be modified.

start slave; view slave status;

Start slave;

show slave status\g;

Three indicators mainly seen:io_running=yes;sql_running=yes;_behind_master=0;

If these three indicators are OK, the master and slave settings are successful.

Please combine the above content with "Learn Linux operation--web Cluster practice with older boys" for better understanding (because it is a note to be taken while studying this book)

This article is from the "Let the past like Smoke" blog, please be sure to keep this source http://kalson.blog.51cto.com/12267030/1953969

MySQL master-slave replication