mysql主從復制實踐之單數(shù)據(jù)庫多實例

1.主從復制數(shù)據(jù)庫實戰(zhàn)環(huán)境準備

       MySQL主從復制實踐對環(huán)境的要求比較簡單，可以是單機單數(shù)據(jù)庫多實例的環(huán)境，也可以是兩臺服務器之間，每臺服務器都部署一個獨立的數(shù)據(jù)庫的環(huán)境。本文以單機數(shù)據(jù)庫多實例的環(huán)境進行實踐。

2.主從復制服務器角色定義

[root@bogon bin]# netstat -lntp | grep 330

tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      24215/mysqld        

tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      24521/mysqld        

tcp        0      0 0.0.0.0:3308                0.0.0.0:*                   LISTEN      24806/mysqld 

3.在主庫master上執(zhí)行如下配置操作
3.1.設置server-id值并開啟binlog功能參數(shù)

       由MySQL主從復制原理我們知道，要實現(xiàn)主從復制，關鍵是要開啟binlog日志功能，所以，要打開主庫的binlog日志參數(shù)。

3.1.1修改主庫的配置文件/data/3306/my.cnf，執(zhí)行vim /data/3306/my.cnf，按如下內容修改參數(shù)：

[mysqld]

server-id = 1                        #用于同步的實例server-id 都不能相同

log-bin = /data/3306/mysql-bin             #開啟binlog日志功能

3.1.2檢查修改后的參數(shù)：

[root@bogon mysql]# grep -E "server-id|log-bin" /data/3306/my.cnf 

log-bin = /data/3306/mysql-bin

server-id = 1

3.1.3重啟主庫mysql服務

[root@bogon mysql]# /data/3306/mysql restart

3.1.4登錄數(shù)據(jù)庫，檢查參數(shù)修改情況：

mysql> show variables like 'server_id';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id     | 1     |

+---------------+-------+

1 row in set (0.00 sec)

mysql> show variables like 'log_bin';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin       | ON    |

+---------------+-------+

1 row in set (0.00 sec)

經(jīng)檢測，主庫的binlog功能以及開啟；

3.2在主庫上建立用于主從復制的賬號

       由主從復制的原理可知，從庫要和主庫同步，需有一個可以連接主庫的賬號，并且這個賬號是在主庫上建立的，權限允許從庫連接主庫并同步數(shù)據(jù)。

3.2.1在主庫創(chuàng)建賬號rep,具體操作如下：

mysql -uroot -p -S /data/3306/mysql.sock         #登錄數(shù)據(jù)庫；

mysql> grant replication slave on *.* to 'rep'@'192.168.1.%' identified by 'lb123456';  #創(chuàng)建rep賬號；

3.2.2檢查賬號創(chuàng)建情況

mysql> use mysql;

mysql> select user,host from user where user='rep';

+------+-------------+

| user | host        |

+------+-------------+

| rep  | 192.168.1.% |

+------+-------------+

1 row in set (0.00 sec)

3.3在主庫上鎖表全備數(shù)據(jù)庫，查看并記錄主庫狀態(tài)信息；

3.3.1對主數(shù)據(jù)庫進行鎖表操作；

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.01 sec)

3.3.2鎖表后查看主庫狀態(tài)

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000004 |      259 |              |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

注意：需記錄該命令顯示的信息，后續(xù)從庫需用到此信息；

3.3.3對主庫數(shù)據(jù)進行全備，具體操作如下：

[root@bogon /]# mkdir -p /server/backup

[root@bogon /]# mysqldump -uroot -S /data/3306/mysql.sock  --events -A -B | gzip > /server/backup/mysql_bak_$(date +%F).sql.gz

[root@bogon /]# cd /server/backup

[root@bogon backup]# ll

total 144

-rw-r--r--. 1 root root 144803 Jun 14 14:59 mysql_bak_2016-06-14.sql.gz

3.3.4再次查看主庫狀態(tài)信息，確保數(shù)據(jù)導出期間，數(shù)據(jù)庫沒有數(shù)據(jù)變更；

[root@bogon backup]# mysql -uroot -S /data/3306/mysql.sock -e "show master status";

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000004 |      259 |              |                  |

+------------------+----------+--------------+------------------+

注意：binlog文件及位置點如果前后一致，即沒有變化，則說明主庫數(shù)據(jù)庫在鎖表導出數(shù)據(jù)前后數(shù)據(jù)庫沒有數(shù)據(jù)變更；

3.3.5解鎖主庫，恢復可寫；

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

4.在從庫slave1上執(zhí)行如下配置操作

4.1.設置server-id值并關閉binlog功能參數(shù)

這里從庫的server-id要和主庫及其他從庫的不同，并且注釋從庫的binlog參數(shù)配置

4.1.1修改主庫的配置文件/data/3307/my.cnf，執(zhí)行vim /data/3307/my.cnf，按如下內容修改參數(shù)：

[mysqld]

server-id = 2                        #用于同步的實例server-id 都不能相同

#log-bin = /data/3306/mysql-bin             #關閉binlog日志功能

4.1.2檢查修改后的參數(shù)：

[root@bogon backup]# grep -E "server-id|log-bin" /data/3307/my.cnf

#log-bin = /data/3307/mysql-bin

server-id = 2

4.1.3重啟主庫mysql服務

[root@bogon mysql]# /data/3307/mysql restart

4.1.4登錄數(shù)據(jù)庫，檢查參數(shù)修改情況：

mysql> show variables like 'server_id';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id     | 2     |

+---------------+-------+

1 row in set (0.00 sec)

mysql> show variables like 'log_bin';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin       | OFF   |

+---------------+-------+

1 row in set (0.00 sec)

4.2把主庫導出的數(shù)據(jù)恢復到從庫slave1上；

[root@bogon /]# cd /server/backup/        #進入備份目錄；

[root@bogon backup]# ll

total 144

-rw-r--r--. 1 root root 144803 Jun 14 14:59 mysql_bak_2016-06-14.sql.gz

[root@bogon backup]# gzip -d mysql_bak_2016-06-14.sql.gz      #解壓備份數(shù)據(jù)庫數(shù)據(jù)

[root@bogon backup]# ll

total 516

-rw-r--r--. 1 root root 527436 Jun 14 14:59 mysql_bak_2016-06-14.sql

[root@bogon backup]# mysql -uroot -S /data/3307/mysql.sock < mysql_bak_2016-06-14.sql #恢復slave1數(shù)據(jù)；

4.3登錄從庫slave1，配置復制參數(shù)

mysql> change master to 

    -> master_host='192.168.1.20',

    -> master_port=3306,

    -> master_user='rep',

    -> master_password='lb123456',

    -> master_log_file='mysql-bin.000004',

    -> master_log_pos=259;

Query OK, 0 rows affected (0.16 sec)

4.4啟動從庫同步開關，查看復制狀態(tài)

mysql> start slave;

Query OK, 0 rows affected (0.04 sec)

主從復制是否成功，最關鍵為下面3項參數(shù)：

[root@bogon backup]# mysql -uroot -S /data/3307/mysql.sock -e "show slave status\G" | grep -E "IO_Running|SQL_Running|Seconds_Behind_Master"

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

        Seconds_Behind_Master: 0

4.5測試主從復制結果

在主庫創(chuàng)建新的數(shù)據(jù)庫，然后觀察從庫的數(shù)據(jù)狀況。

[root@bogon backup]# mysql -uroot -S /data/3306/mysql.sock -e "create database langyabang";   #在主庫上創(chuàng)建新的數(shù)據(jù)庫‘langyabang’;

[root@bogon backup]# mysql -uroot -S /data/3306/mysql.sock -e "show databases like 'langyabang'";

+-----------------------+

| Database (langyabang) |

+-----------------------+

| langyabang            |

+-----------------------+     

#在主庫上查看數(shù)據(jù)庫‘langyabang’創(chuàng)建情況；

[root@bogon backup]# mysql -uroot -S /data/3307/mysql.sock -e "show databases like 'langyabang'";

+-----------------------+

| Database (langyabang) |

+-----------------------+

| langyabang            |

+-----------------------+

#在從庫上查看主從復制情況；

根據(jù)測試主從是同步的。