溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
MySQL主從（MySQL replication），主要用于MySQL的實時備份或者讀寫分離。在配置之前先做一下準備工作，配置兩臺MySQL服務器，如果你的機器不能同時跑兩臺Linux虛擬機，那么可以考慮在同一個機器上跑兩個MySQL服務。
MySQL主從的原理非常簡單，總結一下：
（1）每個主從僅可以設置一個主。
（2）主在執行SQL之后，記錄二進制log文件（bin-log）
（3）從連接主，并獲取主的bin-log，存于本地relay-log，并從上次執行的位置起執行SQL，一旦遇到錯誤則停止同步。
mysql主從配置replication，又叫A,B復制，保證主從數據同步
A --> change data --> bin_log -transfer --> B --> repl_log -->change data
從這幾條replication原理來看，可以有這些推論：
（1）主從間的數據庫不是實時同步，就算網絡連接正常，也存在瞬間，主從數據不一致。
（2）如果主從的網絡斷開，從會在網絡正常后，批量同步。
（3）如果對從進行修改數據，那么很可能從在執行主的bin-log時出現錯誤而停止同步，這是個很危險的操作。所以一般情況下，非常小心的修改從上的數據。
（4）一個衍生的配置是雙主，即互為主從配置，只要雙方的修改不沖突，可以工作良好。
（5）如果需要多主的話，可以用環形配置，這樣任何一個節點的修改都可以同步到所有節點。
（6）可以應用在讀寫分離的場景，用以降低單臺MySQL的I/O
（7）可以是一主多從，也可以是相互主從（主主）
主MySQL（master）：192.168.134.128
從MySQL（slave）：192.168.134.129
1.準備工作：
（1）修改兩個主機的主機名：
主：192.168.134.128
[root@master ~]# hostname master
[root@master ~]# vim /etc/sysconfig/network
hostname=master
從：192.168.134.129
[root@slave~]# hostname slave
[root@slave~]# vim /etc/sysconfig/network
hostname=slave
（2）在兩臺機器上編輯hosts文件：
vim /etc/hosts
都加入兩行：
192.168.134.128 master
192.168.134.129 slave
（3）關閉兩臺機器的防火墻：
關閉SELinux：
setenforce 0
vim /etc/selinux/config
SELINUX=disabled
關閉iptables：
iptables -F
iptables-save
chkconfig iptables off
2.在主從上都安裝MySQL：
主：192.168.134.128
進入源碼包目錄：
[root@master ~]# cd /usr/local/src
下載MySQL安裝包：
[root@master src]# ls
mysql-5.1.73-linux-x86_64-glibc23.tar.gz
解壓MySQL包：
[root@master src]# tar zxvf mysql-5.1.73-linux-x86_64-glibc23.tar.gz
移動并重命名安裝目錄：
[root@master src]# mv mysql-5.1.73-linux-x86_64-glibc23 /usr/local/mysql
查看安裝目錄內容：
[root@master src]# ls /usr/local/mysql/
bin data include lib mysql-test scripts sql-bench
COPYING docs INSTALL-BINARY man README share support-files
創建mysql用戶，不讓其登錄：
[root@master src]# useradd -s /sbin/nologin mysql
進入安裝目錄：
[root@master src]# cd /usr/local/mysql/
拷貝配置文件到/etc目錄下覆蓋原來的my.cnf：
[root@master mysql]# cp support-files/my-small.cnf /etc/my.cnf
cp：是否覆蓋"/etc/my.cnf"？ y
拷貝啟動腳本到/etc/init.d/目錄下重命名為mysqld：
[root@master mysql]# cp support-files/mysql.server /etc/init.d/mysqld
編輯啟動腳本：
[root@master mysql]# vim /etc/init.d/mysqld
定義basedir和datadir:
basedir=/usr/local/mysql
datadir=/data/mysql
創建數據庫存放路徑：
[root@master mysql]# mkdir /data/mysql
配置：
[root@master mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql
WARNING: The host 'master' could not be looked up with resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !
Installing MySQL system tables...
170312 23:59:44 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
OK
Filling help tables...
170312 23:59:44 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
OK
出現兩個OK表示配置成功。
啟動MySQL：
[root@master mysql]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
查看進程：
[root@master mysql]# ps aux | grep mysql
root 1369 0.2 0.0 106060 1484 pts/0 S 01:00 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/master.pid
mysql 1481 1.5 0.5 265280 21612 pts/0 Sl 01:00 0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --user=mysql --log-error=/data/mysql/master.err --pid-file=/data/mysql/master.pid --socket=/tmp/mysql.sock --port=3306
root 1494 0.0 0.0 103248 872 pts/0 S+ 01:00 0:00 grep mysql
查看端口：
[root@master mysql]# netstat -lnp | grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1481/mysqld
unix 2 [ ACC ] STREAM LISTENING 18672 1481/mysqld /tmp/mysql.sock
從：192.168.134.129（主從MySQL的安裝配置過程一模一樣，這里不再贅述）
登錄MySQL有三種方式：
1.使用絕對路徑登錄：
/usr/local/mysql/bin/mysql
2.使用socket登錄：
mysql -S /tmp/mysql.sock
3.使用host+port登錄：
mysql -h227.0.0.1 -P3306
默認都是沒有密碼的，可以使用mysqladmin設置密碼。
3.開始搭建主從MySQL：
主：192.168.134.128
將MySQL加入到環境變量中：
[root@master mysql]# vim /etc/profile.d/mypath.sh
export PATH=$PATH:/usr/local/mysql/bin/
[root@master mysql]# source /etc/profile.d/mypath.sh
登錄MySQL創建數據庫db1：
[root@master mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.73 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
先退出mysql：
mysql> quit
拷貝mysql庫到db1庫：
備份到123.sql：
[root@master mysql]# mysqldump -S /tmp/mysql.sock mysql > 123.sql
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
恢復到db1：
[root@master mysql]# mysql -S /tmp/mysql.sock db1 < 123.sql
再次登錄MySQL，查看db1中的內容：
[root@master mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.73 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use db1;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_db1 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
23 rows in set (0.00 sec)
mysql> quit
Bye
說明db1數據庫創建成功。
編譯配置文件：
[root@master mysql]# vim /etc/my.cnf
打開log-bin前面的注釋：
log-bin=mysql-bin
并在其下面添加一行（表示只對db1做主從）：
binlog-do-db=db1
（多個數據可以用逗號分隔：binlog-do-db=db1,db2,db3，或者使用黑名單形式：binlog-ignore-db=db1）
重啟MySQL：
[root@master mysql]# /etc/init.d/mysqld restart
Shutting down MySQL... SUCCESS!
Starting MySQL. SUCCESS!
查看/data/mysql/下的內容：
[root@master mysql]# ls /data/mysql
db1 ibdata1 ib_logfile0 ib_logfile1 master.err master.pid mysql mysql-bin.000001 mysql-bin.index test
發現二進制日志文件mysql-bin.000001已經生成。
登錄mysql：
[root@master mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.73-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
以replication權限授權給從MySQL上一個用戶slave密碼123abc：
mysql> grant replication slave on *.* to 'slave'@'192.168.134.129' identified by '123abc';
Query OK, 0 rows affected (0.00 sec)
刷新權限：
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
先把表鎖起來：
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
顯示主MySQL的狀態：
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 338 | db1 | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
從：192.168.134.129
將MySQL加入到環境變量中：
[root@slave mysql]# vim /etc/profile.d/mypath.sh
export PATH=$PATH:/usr/local/mysql/bin/
[root@slave mysql]# source /etc/profile.d/mypath.sh
編輯配置文件：
[root@slave mysql]# vim /etc/my.cnf
保證server-id不與主的相同即可：
server-id = 2
（主的server-id = 1）
重啟：
[root@slave mysql]# /etc/init.d/mysqld restart
Shutting down MySQL..... SUCCESS!
Starting MySQL. SUCCESS!
在從上也創建庫db1，
[root@slave mysql]# mysql -e "create database db1"
先將主上備份的123.sql拷貝到從上/usr/local/mysql目錄下：
[root@slave mysql]# scp root@192.168.134.128:/usr/local/mysql/123.sql /usr/local/mysql/123.sql
The authenticity of host '192.168.134.128 (192.168.134.128)' can't be established.
RSA key fingerprint is 7d:f3:cc:4e:ae:cb:3c:31:61:d5:13:8e:04:dc:73:02.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.134.128' (RSA) to the list of known hosts.
root@192.168.134.128's password:
123.sql
將123.sql恢復到db1：
[root@slave mysql]# mysql db1 < 123.sql
保證主從上的數據庫一樣：
登錄mysql先停掉slave：
[root@slave mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.73 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 sec)
配置主從關系（非常關鍵）：
mysql> change master to master_host='192.168.134.128',master_port=3306,master_user='slave',master_password='123abc',master_log_file='mysql-bin.000001',master_log_pos=338;
Query OK, 0 rows affected (0.42 sec)
開啟slave：
mysql> slave start;
Query OK, 0 rows affected (0.00 sec)
查看slave狀態，顯示兩個Yes即為配置成功：
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.134.128
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 338
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000001
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: 338
Relay_Log_Space: 406
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)
ERROR:
No query specified
4.測試MySQL主從：MySQL主從不可以在從上操作，一旦在從上執行一些寫入操作的話，主從機制會發生紊亂。
測試1：在主上刪除一個表，從上也會刪除：
主：192.168.134.128
[root@master mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.73-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
解鎖表：
mysql> unlock tables;
Query OK, 0 rows affected (0.01 sec)
使用db1：
mysql> use db1;
Database changed
查看表：
mysql> show tables;
+---------------------------+
| Tables_in_db1 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
23 rows in set (0.00 sec)
刪除表：
mysql> drop table help_category ;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------------------+
| Tables_in_db1 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
22 rows in set (0.00 sec)
從：192.168.134.129
[root@slave mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.73 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
使用db1：
mysql> use db1;
Database changed
查看表：
mysql> show tables;
+---------------------------+
| Tables_in_db1 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
22 rows in set (0.00 sec)
可以看到從上help_category 也被刪除了。
測試2：在主上創建一個表，從上也會創建：
主：192.168.134.128
創建表：
mysql> create table tb1 (`id` int(4),`name` char(40)) ENGINE=MyISAM DEFAULT CHARSET=gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------------------+
| Tables_in_db1 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| tb1 |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
23 rows in set (0.00 sec)
從：192.168.134.129
mysql> show tables;
+---------------------------+
| Tables_in_db1 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| tb1 |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
23 rows in set (0.00 sec)
可以看到剛創建的表。
測試3：在主上刪除庫，從上也不能再使用
主：192.168.134.128
刪除庫：
mysql> drop database db1;
Query OK, 23 rows affected (0.01 sec)
mysql> show tables;
ERROR 1046 (3D000): No database selected
查看庫：mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
從：192.168.134.129
mysql> show tables;
ERROR 1049 (42000): Unknown database 'db1'
報錯：Unknown database 'db1'
查看庫：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
可以看到也沒有db1庫了。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
