Mysql搭建主從同步

標籤：Mysql主從同步

一、1、實驗環境：兩台伺服器搭建主從服務實現同步
2、實驗拓撲：
主伺服器HK63（IP：192.168.2.104）---------從伺服器HK64（IP：192.168.2.105）
3、實驗思路：
（1）、準備兩台伺服器
（2）、兩台伺服器搭建資料庫（資料庫版本5.1版本）
（3）、建立要同步的資料庫（MA）
（4）、配置主伺服器主設定檔開啟二進位日誌，從伺服器無需開啟，重啟服務
（5）、主伺服器授權從伺服器存取權限
（6）、從伺服器指定主伺服器IP地址、使用者名稱、密碼、連接埠號碼
（7）、主從伺服器均關閉selinux及iptables
（8）、進行測試

二、搭建環境
1、主伺服器HK63資料庫相關配置
（1）、進入伺服器Hk63資料庫建立需同步的資料庫MA

mysql> show databases;
+--------------------+
| Database |
+--------------------+|
information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.10 sec)
mysql> create database MA;Query OK, 1 row affected (0.10 sec)mysql> show databases;
+--------------------+
| Database |
+--------------------+|
information_schema |
| MA |
| mysql |
| test |
+--------------------+4 rows in set (0.00 sec)
mysql> use MA;
Database changed
mysql> create table shuiguo(price int);
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+--------------+|
Tables_in_MA
|+--------------+|
shuiguo
|+--------------+
1 row in set (0.00 sec)

（2）、編輯服務器HK63設定檔
[[email protected] ~]# vim /etc/my.cnf
添加以下粗體內容

[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysqlsymbolic-links=0**log-bin=mysqllog  #啟用二進位日誌，預設存在/var/lib/mysql  下面server-id=1       #本機資料庫ID 標示。其中master_id必須為1到232之間的一個正整數值binlog-do-db=MA   #可以被從伺服器複製的庫。二進位需要同步的資料庫名**[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

（3）、重啟HK63資料庫

[[email protected] ~]# service mysqld restart

（4）、進入資料庫授權從伺服器可存取權限

[[email protected] ~]# mysql -uroot -p123456
mysql> grant replication slave on . to [email protected] identified by "123456";Query OK, 0 rows affected (0.00 sec)
查看主要資料狀態資訊
mysql> show master status;
+-----------------+----------+--------------+------------------+
|File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+----------+--------------+------------------+
|mysqllog.000001 | 258 | MA | |
+-----------------+----------+--------------+------------------+
1 row in set (0.00 sec)
查看二進位檔案存放位置：
[[email protected] ~]# ls /var/lib/mysql/
ibdata1 ib_logfile1 mysql mysqllog.index testib_logfile0
MA mysqllog.000001 mysql.sock

2、配置從伺服器資料庫服務前保證相關資料庫一致性：

[[email protected] ~]# mysqldump -u root -p123456 -A >all.sql

[[email protected] ~]# lsall.sql anaconda-ks.cfg install.log install.log.syslog
[[email protected] ~]# scp all.sql 192.168.2.105:/root/
all.sql 100% 516KB 515.7KB/s 00:00
[[email protected] ~]# mysql -u root -p < all.sql
[[email protected] ~]# mysql -uroot -p123456

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| MA |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)

3、HK64從伺服器相關配置
（1）、測試連接主服務資料庫是否成功

[[email protected] ~]# mysql -u backup -h 192.168.2.104 -p123456

查看當前登入使用者
mysql> select user();
+----------------------+
| user() |
+----------------------+
| [email protected] |
+----------------------+
1 row in set (0.00 sec)
查看資料庫，看不到MA，因為授權使用者只運行複製讀取MA許可權沒有查看許可權
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.00 sec)

（2）設定從伺服器mysql配置
[[email protected] ~]# vim /etc/my.cnf
添加以下標紅參數

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

#在設定檔中寫入以下內容
server-id=2 #從伺服器ID號，不要和主ID相同 ，如果設定多個從伺服器，每個從伺服器必須有一個唯一的server-id值，必須與主伺服器的以及其它從伺服器的不相同。可以認為server-id值類似>於IP地址：這些ID值能唯一識別複製伺服器叢集中的每個伺服器執行個體。
master-host=192.168.2.104 #指定主伺服器IP地址
master-user=backup #制定在主伺服器上可以進行同步的使用者名稱
master-password=123456 #密碼#####以下可以不寫
#master-port = 3306 #同步所用的連接埠
#master-connect-retry=60 #斷點重新連線時間
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
（3）、重啟資料庫服務
[[email protected] ~]# service mysqld restart
（4）、查看從伺服器狀態

[[email protected] ~]# mysql -uroot -p123456

mysql> show slave status\G;
1. row
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.104
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqllog.000001
Read_Master_Log_Pos: 258
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 402
Relay_Master_Log_File: mysqllog.000001
Slave_IO_Running: Yes 可以看到這兩個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: 258
Relay_Log_Space: 558
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: 1 row in set (0.00 sec)

Slave_IO_Running ：一個負責與主機的io通訊
Slave_SQL_Running：負責自己的slave mysql進程

4、測試：
（1）登入主伺服器HK63測試插入資料
[[email protected] ~]# mysql -uroot -p123456

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| MA |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)

mysql> use MA

Database changed
mysql> show tables;
+--------------+
| Tables_in_MA |
+--------------+
| shuiguo |
+--------------+
1 row in set (0.00 sec)

mysql> insert into shuiguo values(10);
Query OK, 1 row affected (0.00 sec)

登入從伺服器HK64查看是否同步成功

[[email protected] ~]# mysql -uroot -p123456

mysql> use MA;

Database changed
mysql> select * from shuiguo;
+-------+
| price |
+-------+
| 10 |
+-------+
1 row in set (0.00 sec)

（2）從伺服器插入資料是否主伺服器資料會同步，進行測試：

mysql> insert into shuiguo values(20);
Query OK, 1 row affected (0.00 sec)
mysql> select * from shuiguo;
+-------+
| price |
+-------+
| 10 |
| 20 |
+-------+

2 rows in set (0.00 sec)
[[email protected] ~]# mysql -uroot -p123456
mysql> use MA
Database changed
mysql> show tables;+--------------+
| Tables_in_MA |
+--------------+
| shuiguo |
+--------------+
1 row in set (0.00 sec)
mysql> select * from shuiguo ;
+-------+
| price |
+-------+
| 10 |
+-------+
1 row in set (0.00 sec)

Mysql搭建主從同步