MySQL Master-slave configuration

MySQL Master-Slave introduction

Preparing, configuring hosts

1. Two primary and standby machines are installed MySQL.

2. Edit the configuration file:

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

Add the following two lines

[mysqld]datadir=/data/mysqlsocket=/tmp/mysql.sockserver-id=130log_bin=weixing1

3. Restart MySQL

4. View the newly generated files: key files

[[email protected] ~]# cd/data/mysql/[[email protected] mysql]# lsauto.cnf ibdata1 ib_logfile1 Performan Ce_schema weixing01.err weixing1.000001 zrlogdb1 ib_logfile0 mysql test weixing01.pid Wei Xing1.index[[email protected] mysql]# ls-lt total dosage 110744-rw-rw----1 mysql mysql 50331648 April 2 22:53 ib_logfile0-rw-r W----1 mysql mysql 12582912 April 2 22:53 ibdata1-rw-rw----1 mysql mysql 66484 April 2 22:53 WEIXING01.ERR-RW-RW----1 MySQL MySQL 5 April 2 22:53 weixing01.pid-rw-rw----1 mysql mysql 18 April 2 22:53 weixing1.index-rw-rw----1 m Ysql MySQL 120 April 2 22:53 weixing1.000001drwx------2 mysql mysql 324 March 22:41 zrlogdrwx------2 MySQL Mys QL 48 March 23:40 db1-rw-rw----1 mysql mysql 56 March 22:25 auto.cnfdrwx------2 mysql mysql 4096 March 2 2 22:22 mysqldrwx------2 mysql mysql 4096 March 22:22 performance_schema-rw-rw----1 mysql mysql 50331648 March 22 22: IB_LOGFILE1DRWX------ 2 MySQL MySQL 6 March 22:22 test 

5. Create test data:

[[email protected] mysql]# mysqldump-uroot-pwei12273914 zrlog >/tmp/zrlog.sqlwarning:using A password on the C Ommand line interface can is insecure. [[email protected] mysql]# du-sh/tmp/zrlog.sql 12k/tmp/zrlog.sql[[email protected] mysql]# mysql-uroot- PWEI12273914-E "CREATE Database Weix" warning:using a password on the command line interface can be insecure. [[email protected] mysql]# mysql-uroot-pwei12273914 Weix </tmp/zrlog.sql warning:using a password on the comma ND line interface can insecure. [[email protected] mysql]# ls-lt total dosage 110692-rw-rw----1 mysql mysql 50331648 April 2 23:00 ib_logfile0-rw-rw----1 Mys QL MySQL 12582912 April 2 23:00 ibdata1-rw-rw----1 mysql mysql 10255 April 2 23:00 weixing1.000001drwx------2 MySQL Mys  QL 324 April 2 23:00 weix-rw-rw----1 mysql mysql 66484 April 2 22:53 weixing01.err-rw-rw----1 MySQL mysql 5 April 2 22:53 weixing01.pid-rw-rw----1 mysql mysql 18 April 2 22:53 weixing1.indexdrWX------2 mysql mysql 324 March 22:41 zrlogdrwx------2 mysql mysql 48 March 23:40 db1-rw-rw----1 MySQL Mys  QL 56 March 22:25 auto.cnfdrwx------2 mysql mysql 4096 March 22:22 mysqldrwx------2 mysql mysql 4096 March   22:22 performance_schema-rw-rw----1 mysql mysql 50331648 March 22:22 ib_logfile1drwx------2 mysql MySQL 6 March 22:22 Test

6. Create a Sync User:

mysql> grant replication slave on *.* to ‘repl‘@‘192.168.188.132‘ identified by ‘weixing01‘;Query OK, 0 rows affected (0.00 sec)

7. Lock the table:

mysql> flush tables with read lock;Query OK, 0 rows affected (0.00 sec)

8. View the current status:

mysql> show master status;+-----------------+----------+--------------+------------------+-------------------+| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-----------------+----------+--------------+------------------+-------------------+| weixing1.000001 |    10467 |              |                  |                   |+-----------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

9. Data backup:

[[email protected] mysql]# mysqldump -uroot -pwei12273914 test > /tmp/test.sqlWarning: Using a password on the command line interface can be insecure.[[email protected] mysql]# mysqldump -uroot -pwei12273914 db1 > /tmp/db1.sqlWarning: Using a password on the command line interface can be insecure.

Configuring the Slave Machine

1. Modify the configuration file:

[[email protected] mysql]# vi /etc/my.cnf

[mysqld]datadir=/data/mysqlsocket=/tmp/mysql.sockserver-id=132

2. Restart the service:

3. Copy the files backed up on the host:

[[email protected] mysql]# scp 192.168.188.130:/tmp/*.sql /tmp/The authenticity of host ‘192.168.188.130 (192.168.188.130)‘ can‘t be established.ECDSA key fingerprint is SHA256:SL6ZOvpHHtEoro8AzeNBXqrM3mr2oXbPaSeXO+LQr1U.ECDSA key fingerprint is MD5:26:0e:7a:96:d6:3a:c5:57:57:2b:6d:1a:1e:42:c9:01.Are you sure you want to continue connecting (yes/no)? yPlease type ‘yes‘ or ‘no‘: yesWarning: Permanently added ‘192.168.188.130‘ (ECDSA) to the list of known hosts.[email protected]‘s password: db1.sql                                            100% 1771   340.5KB/s   00:00    test.sql                                           100% 1258     1.2MB/s   00:00    zrlog.sql                                          100% 9873     5.9MB/s   00:00

4. Go to MySQL: Create table

mysql> create database zrlog;Query OK, 1 row affected (0.00 sec)mysql> create database db1;Query OK, 1 row affected (0.00 sec)

5. Restore the backed up data:

MySQL Master-slave configuration