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

MySQL Master-Slave Introduction

(Data synchronization between two machines)

Main: -->binlog

From: -->relaylog

The master has a log dump thread, which is used to pass binlog to the slave I/O thread.

There are two threads on the slave, one of which is used to synchronize the main binlog and generate relaylog, and the other SQL thread is used to land (execute) sql statements in relaylog.

Master-slave application scenarios:

(1) Do data backup,(master: read and write data, slave: real-time synchronization, when the master is down, slave can also provide service)

(2) It is also a backup. (Customer reads data from this machine (but cannot write from above), relieving pressure on master)

preparations

1. Both machines are equipped with mysql and both open mysql services.

configure the primary

Master: 192.1683.136.133 Slave: 192.168.136.134

1. Modify my.cnf to add server-id=133 and log_bin=canshenglinux1

2. After modifying the configuration file, start or restart the mysqld service

After the reboot, we can see that the file generated under/data/mysql starts with log_bin=canshenglinux1 defined in the configuration file.

These files are very important, otherwise you can't achieve master-slave

3.

Backup and restore mysql library to cansheng library as test data (this library is used as master-slave)

mysqldump -uroot -p123456 mysql > /tmp/mysql.sql

mysql -uroot -p123456 -e "create database cansheng"

mysql -uroot -p123456 cansheng

< /tmp/mysql.sql 4.创建用作同步数据的用户 （1）先进入到mysql （2）再创建同步数据的用户 grant replication slave on *.* to 'repl'@slave_ip identified by '123456'; 5.锁定一下表： flush tables with read lock; （为了同步前数据一致。） show master status (查看一下position和file) (退出mysql) 此时查看一下/data/mysql又是什么库 （一般同步不要同步mysql库，因为里面有很多用户名和密码） 6.备份一下所需要同步的库： mysqldump -uroot -p123456 zrlog >

/tmp/zrlog.sql

configuration from

1. View my.cnf, configure server-id=134, requirements are different from main

2. After modifying the configuration file, start or restart the mysqld service

/etc/init.d/mysqld restart

3. Copy the library that the master needs to synchronize:

scp 192.168.136.133:/tmp/*.sql /tmp/

Create alias temporarily for easy command:

alias 'mysql=/usr/local/mysql/bin/mysql'

alias 'mysqldump=/usr/local/mysql/bin/mysqldump'

4. go to the MySQL

Create the corresponding library:

create database cansheng;

create database zrlog;

create database mysql2;

5. Data recovery:

mysql -uroot cansheng

< /tmp/mysql.sql 6.进入mysql (1)stop slave; (2)change master to master_host='192.168.136.133', master_user='repl', master_password='123456', master_log_fil_log_file='canshenglinux1.000002', master_log_pos=646207; (3)start slave; (4)判定主从是否配置成功 show slave status\G 这样是表示不成功配置成功的要出现两个yes才成功。 不成功的因素： （1）防火墙 systemctl stop firewalld (2)selinux -->

Close sentenforce 0

(3)Is the username correct?

{And my factor is that I specified the wrong username and password when configuring the master, and started over again}

7. Go back to the master and go to mysql to unlock the table

unlock tables;

This completes the master-slave configuration:

Master slave synchronization

(It only needs to be in the master or slave, one of which can be configured.) These parameters are not configured by default, there is no manual configuration, and the default is to synchronize all libraries)

Try to use the last two in the configuration from above, you can avoid ignoring a library or table, avoid data loss when cascading queries, and ignore those that should not be ignored!!

Test whether the master-slave is successful and synchronized!

1. Both machines enter mysql and enter the test database: cansheng2

At this time, clear the comment table of the master; see if the comment table data from the top is also cleared, so as to synchronize.

truncate table comment;

Delete Table Test:

We can no longer perform mysql operations from this machine, otherwise our master and slave will be disconnected.

(Because our position has changed.)

If our master-slave disconnects at this point, we should re-execute

(1)stop slave;

(2)change master to master_host='192.168.136.133', master_user='repl', master_password='123456', master_log_fil_log_file='canshenglinux1.000002', master_log_pos=646207;

(3)start slave;

(4)Determine whether master-slave configuration is successful

show slave status\G

This means that unsuccessful configuration is successful. Two yes's are required to succeed.

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.