mysql主从复制
2017-09-14 本文已影响0人
ALuckyLive
1、将yum源、firewalld、Selinux、同步时间，相关设置配置完成；
2、安装mysql(mariadb);
3、准备两个虚拟机（note1,note2）;
4、配置主从配置文件/etc/my.cnf.d/server.cnf
5、在note1主服务器上创建MYSQL账户；
6、note2从服务器开启复制功能，在note2从服务器上连接至主服务器;
7、在 note1主服务器上创建数据库mydb及表格；
8、查看从服务器是否已同步；
编辑/etc/my.cnf.d/server.cnf
note1;
[mysqld]
Server-id=1
Log-bin=master-log
Skip_name_resolve=ON
note2；
【mysqld】
Server-id=2
Relay-log=relay-log
Skip_name_resolve=ON
主note1 ；创建Mysql账户
[root@note1 mysql]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 245 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'reluser'@'172.16.253.%' IDENTIFIED BY 'replpass';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 498 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> SHOW BINLOG EVENTS IN 'master-log.000003';
+-------------------+-----+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+-----+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------+
| master-log.000003 | 4 | Format_desc | 1 | 245 | Server ver: 5.5.52-MariaDB, Binlog ver: 4 |
| master-log.000003 | 245 | Query | 1 | 423 | GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'reluser'@'172.16.253.%' IDENTIFIED BY 'replpass' |
| master-log.000003 | 423 | Query | 1 | 498 | FLUSH PRIVILEGES |
+-------------------+-----+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
note2;开启从服务器复制功能；
[root@note2 mysql]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> help CHANGE MASTER TO
Name: 'CHANGE MASTER TO'
Description:
Syntax:
CHANGE MASTER TO option [, option] ...
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.253.10',MASTER_USER='reluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master_log.000003',MASTER_LOG_POS=496;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> SHOW SLAVE STATUS\G ---查看从服务器状态；
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.253.10
Master_User: reluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master_log.000003
Read_Master_Log_Pos: 496
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master_log.000003
Slave_IO_Running: No------
Slave_SQL_Running: No----没有真正启动
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 496
Relay_Log_Space: 245
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)
MariaDB [(none)]> START SLAVE
-> ; ------------------启动复制；
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> SHOW SLAVE STATUS\G ---再一次查看状态；
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 172.16.253.10
Master_User: reluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master_log.000003
Read_Master_Log_Pos: 496
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master_log.000003
Slave_IO_Running: Connecting------
Slave_SQL_Running: Yes-----已开启
Replicate_Do_DB:
[root@note2 ~]# cd /var/lib/mysql/
[root@note2 mysql]# ls
aria_log.00000001 ib_logfile0 mysql relay-log.000001 test
aria_log_control ib_logfile1 mysql.sock relay-log.index
ibdata1 master.info performance_schema relay-log.info
[root@note2 mysql]# cat master.info
18
master-log.000003
496
172.16.253.10
repluser
replpass
3306
60
0
0
1800.000
0
[root@note2 mysql]# cat relay-log.info
./relay-log.000001
4
master-log.000003
496
note1;主服务器创建数据库；
MariaDB [(none)]> CREATE DATABASE mydb
-> ;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use mydb;
Database changed
MariaDB [mydb]> CREATE TABLE tbl1 (id INT,name CHAR(50));
Query OK, 0 rows affected (0.01 sec)
MariaDB [mydb]> INSERT INTO tbl1 VALUES (1,'WU MING');
Query OK, 1 row affected (0.00 sec)
note2;从服务器查看；
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 172.16.253.10
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003
Read_Master_Log_Pos: 496
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-log.000003
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
#SHOW TABLES;
#SELECT *FROM tbl1;
