環境描述：
主資料庫master ip：192.168.1.205:3306
從資料庫slave ip：192.168.1.206:3306
###############################################################################
配置步驟：
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資料庫主從同步複制原理[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)