Mysql Master-slave複製簡單配置記錄

搭建了一下mysql master slave的環境
在此做一下簡單記錄
mysql資料庫版本:5.7-18

master與slave均採用瞭如下方式初始化mysql資料庫

mkdir -p /data/mysql
useradd mysql
chown -R mysql:mysql /data/
chown -R mysql:mysql /usr/local/mysql*

/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --datadir=/data/mysql/ --user=mysql --initialize-insecure

Master的my.cnf配置：

Slave的配置檔案：/etc/my.cnf

注意：master-host這個引數5.7已經不支援。
參考：
https://blog.csdn.net/edwzhang/article/details/8819629


初始話完成之後，在master通過mysqldump匯出mysql資料庫
會話1：
[root@mysql01 ~]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18-log MySQL Community Server (GPL)


Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl';
Query OK, 0 rows affected, 1 warning (0.05 sec)


mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)


mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)


mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000017 |      581 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


這個Master Status很重要，在其後的slave配置中需要依賴它

mysqldump -u root -p --all-databases --master-data > /root/dbdump.db

匯出之後，釋放鎖

mysql> unlock tables;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    5
Current database: *** NONE ***

Query OK, 0 rows affected (0.00 sec)

slave端匯入， 匯入之後重啟mysql服務
mysql -u root -p < /root/dbdump.db

在slave端啟用複製：

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.43.151',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='repl',
    -> MASTER_LOG_FILE='mysql-bin.000017',
    -> MASTER_LOG_POS=581;
Query OK, 0 rows affected, 2 warnings (0.30 sec)

mysql>
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

在master端進行測試:

mysql> create database test2;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    7
Current database: *** NONE ***

Query OK, 1 row affected (0.28 sec)

mysql>
mysql> show slave staus
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'staus' at line 1
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000017 |      743 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>
mysql> use test02
No connection. Trying to reconnect...
Connection id:    8
Current database: *** NONE ***

ERROR 1049 (42000): Unknown database 'test02'
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test2              |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test2
Database changed
mysql>
mysql>
mysql> create table mytest01(pid int, nme varchar(100));
Query OK, 0 rows affected (0.34 sec)

mysql>
mysql> insert into mytest01 values(1, 'AAAA');
Query OK, 1 row affected (0.04 sec)

mysql>

如果配置正確，應當可以在slave端看到資料

參考：https://blog.longwin.com.tw/2008/03/mysql_replication_master_slave_set_2008/
        https://blog.wu-boy.com/2008/12/mysql-%E5%AF%A6%E5%81%9A-mysql-master-master-replication-%E5%90%8C%E6%AD%A5/
       https://www.tecmint.com/how-to-setup-mysql-master-slave-replication-in-rhel-centos-fedora/

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8520577/viewspace-2156333/，如需轉載，請註明出處，否則將追究法律責任。