共计 14831 个字符，预计需要花费 38 分钟才能阅读完成。
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 -h127.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 库了。
本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-03/142193.htm
