MySQL主从搭建与配置

jfgdf

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
[iyunv@master ~]# hostname master
[iyunv@master ~]# vim /etc/sysconfig/network
hostname=master
从：192.168.134.129
[iyunv@slave~]# hostname slave
[iyunv@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
进入源码包目录：
[iyunv@master ~]# cd /usr/local/src
下载MySQL安装包：
[iyunv@master src]# ls
mysql-5.1.73-linux-x86_64-glibc23.tar.gz
解压MySQL包：
[iyunv@master src]# tar zxvf mysql-5.1.73-linux-x86_64-glibc23.tar.gz
移动并重命名安装目录：
[iyunv@master src]# mv mysql-5.1.73-linux-x86_64-glibc23 /usr/local/mysql
查看安装目录内容：
[iyunv@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用户，不让其登录：
[iyunv@master src]# useradd -s /sbin/nologin mysql
进入安装目录：
[iyunv@master src]# cd /usr/local/mysql/
拷贝配置文件到/etc目录下覆盖原来的my.cnf：
[iyunv@master mysql]# cp support-files/my-small.cnf /etc/my.cnf
cp：是否覆盖"/etc/my.cnf"？ y
拷贝启动脚本到/etc/init.d/目录下重命名为mysqld：
[iyunv@master mysql]# cp support-files/mysql.server /etc/init.d/mysqld
编辑启动脚本：
[iyunv@master mysql]# vim /etc/init.d/mysqld
定义basedir和datadir:
basedir=/usr/local/mysql
datadir=/data/mysql
创建数据库存放路径：
[iyunv@master mysql]# mkdir /data/mysql
配置：
[iyunv@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：
[iyunv@master mysql]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
查看进程：
[iyunv@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
查看端口：
[iyunv@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加入到环境变量中：
[iyunv@master mysql]# vim /etc/profile.d/mypath.sh
export PATH=$PATH:/usr/local/mysql/bin/
[iyunv@master mysql]# source /etc/profile.d/mypath.sh
登录MySQL创建数据库db1：
[iyunv@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：
[iyunv@master mysql]# mysqldump -S /tmp/mysql.sock mysql > 123.sql
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
恢复到db1：
[iyunv@master mysql]# mysql -S /tmp/mysql.sock db1 < 123.sql
再次登录MySQL，查看db1中的内容：
[iyunv@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数据库创建成功。
编译配置文件：
[iyunv@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：
[iyunv@master mysql]# /etc/init.d/mysqld restart
Shutting down MySQL... SUCCESS!
Starting MySQL. SUCCESS!
查看/data/mysql/下的内容：
[iyunv@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：
[iyunv@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加入到环境变量中：
[iyunv@slave mysql]# vim /etc/profile.d/mypath.sh
export PATH=$PATH:/usr/local/mysql/bin/
[iyunv@slave mysql]# source /etc/profile.d/mypath.sh
编辑配置文件：
[iyunv@slave mysql]# vim /etc/my.cnf
保证server-id不与主的相同即可：
server-id       = 2
（主的server-id       = 1）
重启：
[iyunv@slave mysql]# /etc/init.d/mysqld restart
Shutting down MySQL..... SUCCESS!
Starting MySQL. SUCCESS!
在从上也创建库db1，
[iyunv@slave mysql]# mysql -e "create database db1"
先将主上备份的123.sql拷贝到从上/usr/local/mysql目录下：
[iyunv@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：
[iyunv@slave mysql]# mysql db1 < 123.sql
保证主从上的数据库一样：
登录mysql先停掉slave：
[iyunv@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
[iyunv@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
[iyunv@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库了。