MySQL資料庫主從同步

標籤：

環境描述：

主要資料庫master  ip：192.168.1.205:3306

從資料庫slave     ip：192.168.1.206:3306

 

源碼安裝Mysql-5.5.46請移步：http://linuxzkq.blog.51cto.com/9379412/1700082，這裡不在描述

###############################################################################

配置步驟：

a.主要資料庫配置步驟

1、開啟主要資料庫的binlog（二進位日誌功能），並設定server-id

2、建立用於同步資料的帳號rep

3、鎖表並查看當前日誌名稱和位置(pos)

4、備份當前主要資料庫的全部資料（全備）

5、解鎖主要資料庫，讓主要資料庫繼續提供服務

6、繼續往主要資料庫寫資料

 

b.從資料庫配置步驟

1、把主要資料庫備份的全備資料恢複到從資料庫

2、設定從資料庫server-id，並確保這個ID沒有被別的MySQL服務所使用。

3、在從資料庫上設定主從同步的相關資訊，如主要資料庫伺服器的IP地址、連接埠號碼、同步帳號、密碼、binlog檔案名稱、binlog位置(pos)點

4、開始主從同步start slave；

5、查看是否同步成功，show slavestatus\G;

############################################################################

 

 

 

主要資料庫配置:

##################################################################

開啟主要資料庫的binlog（二進位日誌功能），並設定server-id

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

server-id       = 1

log-bin=mysql-bin

[[email protected] ~]# egrep "server-id|log-bin" /etc/my.cnf

server-id       = 1

log-bin=mysql-bin

################################################################## 

重啟資料庫

[[email protected] ~]# service mysqld restart

Shutting down MySQL.......... SUCCESS!

Starting MySQL.......... SUCCESS!

[[email protected] ~]# netstat -tunlp|grep mysqld

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

[[email protected] ~]# lsof -i:3306

COMMAND   PID  USER  FD   TYPE DEVICE SIZE/OFF NODENAME

mysqld      16530 mysql 11u  IPv4  52627     0t0  TCP *:mysql (LISTEN)

################################################################## 

查看是否記錄二進位日誌

[[email protected] ~]# ll /application/data/mysql-bin.000001

-rw-rw----. 1 mysql mysql 2629 Oct  2 15:35 /application/data/mysql-bin.000001  #有此檔案，證明在記錄二進位日誌

################################################################## 

主要資料庫中建立測試用的資料庫和表

mysql> create database test;

Query OK, 1 row affected (0.02 sec)

mysql> use test;

Database changed

mysql> create table test(id int(3),namechar(10));

Query OK, 0 rows affected (0.14 sec)

mysql> insert into test values(001,‘mytest‘);

Query OK, 1 row affected (0.14 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.02 sec)

################################################################## 

查看是否開啟binlog（二進位日誌功能）

mysql> show variables like ‘log_bin‘;

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

| Variable_name | Value |

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

| log_bin     | ON   |

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

1 row in set (0.00 sec)

##################################################################  

建立用於同步資料的帳號rep

mysql> grant replication slave on *.* to ‘rep‘@‘192.168.1.%‘identified by ‘rep123456‘;

mysql> flush privileges;

mysql> select user,host from mysql.user;                

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

| user | host      |

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

| root | 127.0.0.1   |

| rep  | 192.168.1.% |

| root | localhost   |

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

3 rows in set (0.04 sec)

##################################################################

鎖表

mysql> flush table withread lock; 

Query OK, 0 rows affected (0.00 sec)

##################################################################

查看當前二進位日誌名稱和位置(pos)

mysql> show master status;

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

| File         | Position | Binlog_Do_DB |Binlog_Ignore_DB |

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

| mysql-bin.000001 |    107 |         |             |

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

1 row in set (0.03 sec)

##################################################################

備份當前主要資料庫的資料

[[email protected] ~]# mysqldump-uroot  -p87003891214 -A -B -F--master-data=2 --events | gzip > /tmp/mysql_bak.$(date +%F).sql.gz 

[[email protected] ~]# ll /tmp

total 156

drwxrwxrwt. 2 tomcat tomcat   4096 Oct 2 09:33 hsperfdata_tomcat

-rw-r--r--. 1 root   root  151317 Oct  2 20:08mysql_bak.2015-10-02.sql.gz

-rwxrwxrwt. 1 root   root       0 Aug 13 11:55 yum.log

解鎖主要資料庫

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

################################################################## 

 

 

從資料庫配置:

######################################################

把主要資料庫備份的全備資料恢複到從資料庫

 

[[email protected] ~]# scp /tmp/[email protected]:~

[email protected]‘s password:

mysql_bak.2015-10-02.sq 100% 148KB 147.8KB/s   00:00

[[email protected] ~]# ll mysql_bak.2015-10-02.sql.gz

-rw-r--r--. 1 root root 151317 Aug 19 07:37 mysql_bak.2015-10-02.sql.gz

[[email protected] ~]# gzip -d mysql_bak.2015-10-02.sql.gz

[[email protected] ~]# mysql -uroot -p87003891214 < mysql_bak.2015-10-02.sql

[[email protected] ~]# mysql -uroot -p87003891214 -e "showdatabases;"

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

| Database        |

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

| information_schema |

| mysql         |

| performance_schema |

| test          |

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

[[email protected] ~]# mysql -uroot -p87003891214 -e "usetest;select * from test;"       

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

| id   | name  |

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

|    1 | mytest|

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

###############################################################

設定從資料庫server-id，並確保這個ID沒有被別的MySQL服務所使用

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

server-id       = 2

[[email protected] ~]# grep "server-id" /etc/my.cnf

server-id       = 2

##############################################################

在從資料庫上設定主從同步的相關資訊，如主要資料庫伺服器的IP地址、連接埠號碼、同步帳號、密碼、binlog檔案名稱、binlog位置(pos)點

mysql> CHANGE MASTER TO

MASTER_HOST=‘192.168.1.205‘,

MASTER_PORT=3306,

MASTER_USER=‘rep‘,

MASTER_PASSWORD=‘rep123456‘,

MASTER_LOG_FILE=‘mysql-bin.000001‘,

MASTER_LOG_POS=107;

#############################################################

開始主從同步start slave；

mysql> start slave;

查看是否同步成功，show slave status\G;

mysql> show  slavestatus\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting formaster to send event

Master_Host: 192.168.1.205

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File:mysql-bin.000008

Read_Master_Log_Pos: 107

Relay_Log_File:web2-relay-bin.000014

Relay_Log_Pos: 253

Relay_Master_Log_File:mysql-bin.000008

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

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: 107

Relay_Log_Space: 451

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: 0

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: 1

1 row in set (0.01 sec)

 

ERROR:

No query specified

其中Slave_IO_Running 與 Slave_SQL_Running 的值都必須為YES,代表主從同步成功了。

#############################################################

繼續往test表中插入資料

mysql> use test;

Database changed

mysql> insert into test values(002,‘mytest2‘);

Query OK, 1 row affected (0.01 sec)

 

mysql> insert into test values(003,‘mytest3‘);

Query OK, 1 row affected (0.05 sec)

 

mysql> insert into test values(004,‘mytest4‘);

Query OK, 1 row affected (0.04 sec)

 

查看從資料庫的表

 

mysql> use test;

Database changed

mysql> select * from test;

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

| id   | name   |

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

|    1 | mytest |

|    2 | mytest2 |

|    3 | mytest3 |

|    4 | mytest4 |

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

4 rows in set (0.00 sec)

 

MySQL資料庫主從同步