配置Mysql資料庫的主從同步教程

配置Mysql資料庫的主從同步(一主一從)

一、主庫開啟BINLOG、server-id

[root@Master-Mysql ~]# grep -E "server-id|log-bin" /etc/my.cnf
log-bin = /usr/local/mysql/data/mysql-bin
server-id = 1
mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------------+
| Variable_name                   | Value                                 |
+---------------------------------+---------------------------------------+
| log_bin                         | ON                                    |
| log_bin_basename                | /usr/local/mysql/data/mysql-bin       |
| log_bin_index                   | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                   |
| log_bin_use_v1_row_events       | OFF                                   |
| sql_log_bin                     | ON                                    |
+---------------------------------+---------------------------------------+
6 rows in set (0.01 sec)
mysql> show variables like '%server_id%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| server_id      | 1     |
| server_id_bits | 32    |
+----------------+-------+
2 rows in set (0.00 sec)
備忘：以上兩個資訊必須在[mysqld]模組下！！！

二、給從庫授權

mysql> grant replication slave on *.* to byrd@'192.168.199.%' identified by 'admin';
mysql> flush privileges;
mysql> select user,host from mysql.user;
+------+---------------+
| user | host          |
+------+---------------+
| root | 127.0.0.1     |
| byrd | 192.168.199.% |
| root | ::1           |
| root | lamp          |
| root | localhost     |
+------+---------------+
5 rows in set (0.00 sec)
鎖表前建立點資料：

mysql> create database hitest;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hitest             |
+--------------------+
6 rows in set (0.00 sec)
mysql> use hitest;
mysql> create table test(
    -> id int(4) not null primary key auto_increment,
    -> name char(20) not null
    -> );
Query OK, 0 rows affected (1.80 sec)
mysql> show tables ;
+------------------+
| Tables_in_hitest |
+------------------+
| test             |
+------------------+
mysql> insert into test(id,name) values(1,'zy');
mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | zy   |
+----+------+
三、鎖表、備份、解鎖

mysql> flush table with read lock;    #鎖表
mysql> show variables like '%timeout%';    #鎖表時間
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| interactive_timeout         | 28800    |
| wait_timeout                | 28800    |
+-----------------------------+----------+
12 rows in set (0.06 sec)
mysql> show master status;    #binlog日誌位置
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |     1305 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.03 sec)
[root@Master-Mysql ~]# /usr/local/mysql/bin/mysqldump -uroot -p'' -B -A |gzip >/tmp/all.sql.gz    #新視窗備份
Enter password:
mysql> unlock table;    #解鎖
###############解鎖後主庫操作如下：###############
mysql> use hitest
mysql> insert into test(id,name) values(2,'binghe');
mysql> select * from test;
+----+--------+
| id | name   |
+----+--------+
|  1 | zy     |
|  2 | binghe |
+----+--------+
mysql> create database hxy;
###############解鎖後主庫操作完成~###############
備忘：備份資料需要重新開啟新視窗，不然鎖表就自動失效；

四、主庫匯入到從庫

################主庫操作################
[root@Master-Mysql tmp]# ll
-rw-r--r--. 1 root  root  162236 Jul  8 21:30 all.sql.gz
[root@Master-Mysql tmp]# gzip -d all.sql.gz
[root@Master-Mysql tmp]# ll
-rw-r--r--. 1 root  root  590351 Jul  8 21:30 all.sql
################主庫完成################

##備忘：將主庫匯出的all.sql通過scp、ssh、sftp等方式拷貝到從程式庫伺服器，此處略##

[root@Slave-Mysql ~]# grep log-bin /etc/my.cnf
#log-bin = /usr/local/mysql/data/mysql-bin
[root@Slave-Mysql ~]# grep server-id /etc/my.cnf
server-id = 2
[root@Slave-Mysql ~]# /etc/init.d/mysqld restart
[root@Slave-Mysql tmp]# /usr/local/mysql/bin/mysql -uroot -p'admin' </tmp/all.sql
Warning: Using a password on the command line interface can be insecure.
[root@Slave-Mysql tmp]# /usr/local/mysql/bin/mysql -uroot -p'admin'
mysql> use hitest;
mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | zy   |
+----+------+
1 row in set (0.00 sec)
六、從庫配置資訊

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.199.177',
    -> MASTER_PORT=3306,
    -> MASTER_USER='byrd',
    -> MASTER_PASSWORD='admin',
    -> MASTER_LOG_FILE='mysql-bin.000004',
    -> MASTER_LOG_POS=1305;
Query OK, 0 rows affected, 2 warnings (1.96 sec)
[root@Slave-Mysql ~]# ll /usr/local/mysql/data/master.info
##備忘：master.info記錄MASTER的相關資訊！
七、啟動從庫同步

mysql> start slave;
mysql> show slave status＼G
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            Seconds_Behind_Master: 0
八、結果測試

mysql> use hitest;
mysql> select * from test;
+----+--------+
| id | name   |
+----+--------+
|  1 | zy     |
|  2 | binghe |
+----+--------+
2 rows in set (0.00 sec)
[root@Master-Mysql ~]# /usr/local/mysql/bin/mysql -uroot -p'' -e "create database zhihu;"    #主庫建立了一個zhihu的資料庫
Enter password:
[root@Slave-Mysql ~]# /usr/local/mysql/bin/mysql -uroot -p'' -e "show databases like 'zhihu'";
Enter password:
+------------------+
| Database (zhihu) |
+------------------+
| zhihu            |
+------------------+

配置Mysql資料庫的主從同步(雙主)

已經配置好的：

主庫：192.168.199.177
從庫：192.168.199.178

[root@Master-Mysql ~]# egrep "server-id|log-slave|log-bin|auto_increment|slave-skip-errors" /etc/my.cnf
log-bin = /usr/local/mysql/data/mysql-bin    #必須
server-id = 1    #必須
log-slave-updates    #必須
auto_increment_increment = 2    #必須
auto_increment_offset = 1    #必須
slave-skip-errors = 1032,1062,1007    #非必須，建議
########################主庫、從庫分隔字元########################
[root@Slave-Mysql data]# egrep "server-id|log-slave|log-bin|auto_increment|slave-skip-errors|read-only" /etc/my.cnf
#log-bin = /usr/local/mysql/data/mysql-bin
server-id = 2
log-slave-updates
log-bin = /usr/local/mysql/data/mysql-bin
#read-only    #雙主，此選項要注釋掉
slave-skip-errors = 1032,1062,1007
auto_increment_increment = 2    #ID自增間隔
auto_increment_offset = 2    #ID初始位置
192.168.199.178:

mysql> stop slave;
mysql> flush table with read lock;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
mysql> system /usr/local/mysql/bin/mysqldump -uroot -p'' -A -B >/tmp/192.168.199.178.sql   #如果主、從一致非必須
mysql> unlock tables;    #同上
mysql> system ls -l /tmp/
-rw-r--r--.  1 root  root   2887406 Jul 12 22:24 192.168.199.178.sql
mysql> start slave;
192.168.199.177:

[root@Master-Mysql ~]# /usr/local/mysql/bin/mysql -uroot -p'' < /tmp/192.168.199.178.sql    #如果主、從一致非必須
mysql> update mysql.user set password=PASSWORD('admin') where user='root';
[root@Master-Mysql ~]# cat |/usr/local/mysql/bin/mysql -uroot -p'admin' <<EOF    #必須
> CHANGE MASTER TO
> MASTER_HOST='192.168.199.178',
> MASTER_PORT=3306,
> MASTER_USER='byrd',
> MASTER_PASSWORD='admin',
> MASTER_LOG_FILE='mysql-bin.000004',
> MASTER_LOG_POS=120;
> EOF
mysql> start slave;
mysql> show slave status＼G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.199.178
                  Master_User: byrd
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 938
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 1101
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                   Last_Errno: 0
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 938
              Relay_Log_Space: 1275
              Until_Condition: None
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
               Last_SQL_Errno: 0
             Master_Server_Id: 2
                  Master_UUID: 34d672c3-d292-11e3-9ff5-00155dc7834c
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
測試：
192.168.199.177:

mysql> use hitest;
mysql> CREATE TABLE `ces` (
    -> `REL_ID` bigint(12) NOT NULL auto_increment COMMENT 'id',
    -> `TITLE` varchar(255) NOT NULL COMMENT 'biaoti',
    -> PRIMARY KEY (`REL_ID`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
mysql> insert into ces(TITLE) values('test');
mysql> insert into ces(TITLE) values('test');
mysql> insert into ces(TITLE) values('test');
mysql> insert into ces(TITLE) values('test25');
mysql> select * from ces;
+--------+-------+
| REL_ID | TITLE |
+--------+-------+
|      1 | test  |
|      3 | test  |
|      5 | test  |
|     25 | test25|
+--------+--------+
3 rows in set (0.03 sec)
192.168.199.178:

mysql> use hitest;
mysql> insert into ces(TITLE) values('test26');
mysql> insert into ces(TITLE) values('test28');
mysql> insert into ces(TITLE) values('test30');
mysql> select * from ces;
+--------+--------+
| REL_ID | TITLE  |
+--------+--------+
|      1 | test   |
|      3 | test   |
|      5 | test   |
|     26 | test26 |
|     28 | test28 |
|     30 | test30 |
+--------+--------+
17 rows in set (0.00 sec)

說明：如果一主、一叢已經做好，只要知道從庫位置點（show master status;）、然後之前主庫執行（CHANGE MASTER）、之前主庫開啟slave（start slave）即可。其中Database Backup等步驟可以省略，如果主從有一些資料庫不一致則同上操作！