MySQL的主从复制方案，是数据的传输。只不过MySQL无需借助第三方工具，而是其自身同步复制的功能，另外一点，MySQL的主从复制并不是磁盘上直接同步，而是逻辑binlog日志同步到本地再应用执行的过程。
一、MySQL主从同步的简要操作过程:
1)从库开启两个线程，分别是IO线程和SQL线程，而主库只开启有一个IO线程
2)在从库上配置连接主库的IP地址、端口号、用同步的用户名、密码、准备更新的二进制文件名字、和准备更新的位置点(pos点，即从那个位置点开始更新)
3)在启动slave-id开关之前要不主库的位置之前的binlog文件拷贝到从库上，否则在该位置点之前的数据从库上就没有了，然后在主库备份的时候这个点就是从库change master之后的备份点，所以在开启开关之前确保主从库数据一致
4)在主库打开开关之前创建用户从库同步的账户(用户名和密码)
5)主库要打开binlog开关，否则无法实现同步
6)从库打开开关的过程实际上IO和SQL线程都工作的过程
二、主从同步原理简述
1)slave服务器上执行start slave，开启主从复制开关
2)此时，slave服务器的IO线程会通过在Master上授权的复制用户权限向Master服务器发起请求连接，并请求指定的Binlog日志文件和指定的Binlog日志文件的位置(日志文件名和位置就是在配置主从复制服务时执行的change master 命令时指定的)之后开始等待master服务器发送Binlog日志内容；
3)Master 服务器接收来自Slave服务器的IO线程的请求后，Master服务器向负责复制的IO线程根据Slave的IO线程的请求信息读取slave指定要获取的Binlog日志文件和指定位置之后的Binlog日志信息，然后返回给Slave端的IO线程，返回的信息中除了Binlog日志内容外，还有本次返回日志内容后在Master服务器端的新的Binlog文件名称以及在Binlog中的下一个指定更新位置
4)当slave服务器的IO线程获取到来自Master服务器上的IO线程发送日志内容及日志文件位置信息之后，将Binlog日志内容依次写入到Slave端本身的Relay Log(即中继日志)文件(MySQL-relay-bin.xxxxx)的最末端，并将新的Binlog文件名和位置记录到master-info文件中，以便下一次读取Master端新Binlog日志时能够告诉Master服务器需要从新的Binlog日志的哪个位置开始请求新的Binlog日志内容
5)此时Slave服务器端的SQL线程会实时的检测本地Relay Log中新增加的日志内容，然后及时的把Log文件中的内容解析成在Master端曾经执行的SQL语句的内容，并在自身Slave服务器上按照语句的顺序执行应用这些SQL语句，应用完成后清理应用过的日志.
6)经过上面的过程就可以确保在Master端和slave端执行了同样的SQL语句。当复制状态正常情况下，Master端和Slave端的数据是完全一样的，MySQL的同步机制是在一些特殊情况下的
三、口语化描述主从同步原理:
1)从库上的IO 进程不停的向主库发送请求，请求信息包括用户名、密码、IP地址、端口、以及要读取的主库哪个位置点的二进制日志文件
2)主库上的IO进程检查来自从库的IO进程的请求，核对权限和其他的相关信息，然后给从库的IO进程回传从库指定的二进制文件和更新位置点
3)此时从库接收到主库回传过来的二进制文件存放到中继日志(即relay-log)中，并且从主库传过来的二进制日志中取得下次要更新的二进制文件名和位置点存储到master.info文件里，然后继续向主库发送IO请求；
4)由于从库上的SQL进程对本地的中继日志(即relay-log)是处于时时监听状态，所以只要中继日志有变化，就会读取其中变化的部分，然后将变化的部分经过转换写到从库的存储引擎里面，至此完成一次数据同步。
四、实战操作
1)实验环境准备
OS：CentOS release 6.4
主服务器(master)IP:10.0.0.7
从服务器(slave)IP:10.0.0.8
MySQL版本:mysql-5.6.20.tar.gz
2)MySQL数据库的安装
a、创建用户(用户组)[root@master ~]# groupadd mysql
[root@master ~]# useradd mysql -s /sbin/nologin -M -g mysql
[root@master ~]#
b、安装cmake编译工具[root@master ~]# cd /home/www/tools/
[root@master tools]# ll
total 219928
-rw-r--r-- 1 root root 5490501 Dec 3 2014 cmake-3.0.2.tar.gz
-rw-r--r-- 1 root root 32979820 Sep 18 2014 mysql-5.6.20.tar.gz
[root@master tools]# tar xf cmake-3.0.2.tar.gz
[root@master tools]# cd cmake-3.0.2
[root@master cmake-3.0.2]# ./configure
[root@master cmake-3.0.2]# gmake
[root@master cmake-3.0.2]# gmake install
[root@master cmake-3.0.2]# cd ..
c、安装MySQL[root@master tools]# tar xf mysql-5.6.20.tar.gz
[root@master tools]# cd mysql-5.6.20
[root@master mysql-5.6.20]#
cmake \
-DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.20 \ -DMYSQL_DATADIR=/application/mysql-5.6.20/data \ -DMYSQL_UNIX_ADDR=/application/mysql-5.6.20/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \ -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \ -DENABLED_LOCAL_INFILE=ON \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_FEDERATED_STORAGE_ENGINE=1 \ -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ -DWITH_EXAMPLE_STORAGE_ENGINE=1 \ -DWITH_PARTITION_STORAGE_ENGINE=1 \ -DWITH_FAST_MUTEXES=1 \ -DWITH_ZLIB=bundled \ -DENABLED_LOCAL_INFILE=1 \ -DWITH_READLINE=1 \ -DWITH_EMBEDDED_SERVER=1 \
-DWITH_DEBUG=0
[root@master tools]
注意:换行符("\")与参数后面要有一个空格，否则安装会报错，无法创建目录
Install the project...
-- Install configuration: "RelWithDebInfo"
CMake Error at cmake_install.cmake:36 (FILE):
file cannot create directory:
如果出现如下错误:
CMake Error at cmake/readline.cmake:85 (MESSAGE):
Curses library not found. Please install appropriate package,
remove CMakeCache.txt and rerun cmake.On Debian/Ubuntu, package name is libncurses5-dev, on Redhat and derivates it is ncurses-devel.
Call Stack (most recent call first):
根据以上提示说明缺少ncurses-devel依赖包，直接安装即可：
[root@master mysql-5.6.20]# yum -y install ncurses ncurses-devel
然后再次执行上述编译操作，仍然报错:
-- Could NOT find Curses (missing: CURSES_LIBRARY CURSES_INCLUDE_PATH)
CMake Error at cmake/readline.cmake:85 (MESSAGE):
Curses library not found. Please install appropriate package,
remove CMakeCache.txt and rerun cmake.On Debian/Ubuntu, package name is libncurses5-dev, on Redhat and derivates it is ncurses-devel.
Call Stack (most recent call first):
cmake/readline.cmake:128 (FIND_CURSES)
cmake/readline.cmake:202 (MYSQL_USE_BUNDLED_EDITLINE)
CMakeLists.txt:427 (MYSQL_CHECK_EDITLINE)
解决方法删除CMakeCache.txt文件，再次编译[root@master mysql-5.6.20]# rm CMake
CMakeCache.txt CMakeFiles/ CMakeLists.txt
[root@master mysql-5.6.20]# rm CMakeCache.txt
rm: remove regular file `CMakeCache.txt'? y
[root@master mysql-5.6.20]# cmake -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.20 -DMYSQL_DATADIR=/application/mysql-5.6.20/data -DMYSQL_UNIX_ADDR=/application/mysql-5.6.20/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii -DENABLED_LOCAL_INFILE=ON -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_EXAMPLE_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DWITH_FAST_MUTEXES=1 -DWITH_ZLIB=bundled -DENABLED_LOCAL_INFILE=1 -DWITH_READLINE=1 -DWITH_EMBEDDED_SERVER=1 -DWITH_DEBUG=0
[root@master mysql-5.6.20]# make && make install
[root@master mysql-5.6.20]# pwd
/home/www/tools/mysql-5.6.20
[root@master mysql-5.6.20]# cd /application/
[root@master application]# ll
total 12
drwxr-xr-x 13 root root 4096 Mar 2 17:37 mysql-5.6.20
[root@master application]# ln -s mysql-5.6.20 mysql
[root@master application]# ll
total 12
lrwxrwxrwx 1 root root 12 Mar 2 17:38 mysql -> mysql-5.6.20
drwxr-xr-x 13 root root 4096 Mar 2 17:37 mysql-5.6.20
[root@master application]#
3)拷贝配置文件和配置环境变量[root@master application]# cd mysql-5.6.20/
[root@master mysql-5.6.20]# cp support-files/
binary-configure magic my-default.cnf mysqld_multi.server mysql-log-rotate mysql.server
[root@master mysql-5.6.20]# cp support-files/my-default.cnf /etc/my.cnf
cp: overwrite `/etc/my.cnf'? y
[root@master mysql-5.6.20]# echo 'export PATH=/application/mysql/bin:$PATH'>>/etc/profile
[root@master mysql-5.6.20]# source /etc/profile
[root@master mysql-5.6.20]# echo $PATH
/application/mysql/bin:/application/redis/bin:/application/redis/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
[root@master mysql-5.6.20]#
[root@master mysql-5.6.20]# cd /application/
[root@master application]# chown -R mysql.mysql /application/mysql/data/
[root@master application]# chown -R 1777 /tmp
[root@master application]# cd /application/mysql/scripts/
[root@master scripts]#
[root@master scripts]# ./mysql_install_db --basedir=/application/mysql --datadir=/application/mysql/data/ --user=mysql
拷贝启动脚本并赋予执行权限[root@master scripts]# cd /home/www/tools/mysql-5.6.20
[root@master mysql-5.6.20]# cp support-files/mysql.server /etc/init.d/mysqld
[root@master mysql-5.6.20]# chmod +x /etc/init.d/mysqld
[root@master mysql-5.6.20]#
启动mysql服务并验证是否成功:[root@master mysql-5.6.20]# /etc/init.d/mysqld start
Starting MySQL..... SUCCESS!
[root@master mysql-5.6.20]# netstat -lnput|grep 3306
tcp 0 0 :::3306 :::* LISTEN 3004/mysqld
[root@master mysql-5.6.20]#
登录服务器验证是否成功:[root@master mysql-5.6.20]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.20 Source distribution
Copyright (c) 2000, 2014, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.13 sec)
mysql>
至此主服务器上MySQL安装部署完成，从服务器执行同样的操作
五)配置主从同步
修改master上配置文件my.cnf具体内容如下:[root@master data]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
basedir = /application/mysql-5.6.20
datadir = /application/mysql-5.6.20/data
port = 3306
server_id = 1
socket = /application/mysql/tmp/mysql.sock
log-bin = /application/mysql/data/mysql-bin
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
# #safe-updates
#
# [myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
#
# [mysqlhotcopy]
interactive-timeout
[root@master data]#
修改slave上配置文件my.cnf具体内容如下:[root@slave etc]# cat my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
basedir = /application/mysql-5.6.20
datadir = /application/mysql-5.6.20/data
port = 3306
server_id = 3
socket = /application/mysql/tmp/mysql.sock
log-bin = /application/mysql/data/mysql-bin
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
# #safe-updates
#
# [myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
#
# [mysqlhotcopy]
interactive-timeout
[root@slave etc]#
主从配置文件区别在于server_id的值不同，这里主从配置在两台不同服务器，所以只需要server-id不同即可,另外在主服务器(master)上开启二进制日志记录功能,从服务器可以开启也可以不开启[root@master data]# egrep "log-bin|server_id" /etc/my.cnf
server_id = 1
log-bin = /application/mysql/data/mysql-bin
[root@master data]#
注意:
1、server_id和log-bin参数位置一定要放在【msyqld】模块内
2、server-id的值使用服务器ip地址的最后8位如19，目的是避免不同机器或者实例ID重复(不适合多实例)
3、要先在my.cnf配置文件中查找相关参数，并按照要求修改，不存在时添加参数，切记，参数不能重复
4、修改my.cnf配置后需要重启数据库，命令为:/etc/init.d/mysqld restart,注意要确认真正重启了
重启MySQL服务，查看是否生效(这里主从都重启)[root@master data]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL...... SUCCESS!
[root@master data]#
[root@master data]# ll
total 110676
-rw-rw---- 1 mysql mysql 56 Mar 2 17:46 auto.cnf
-rw-rw---- 1 mysql mysql 12582912 Mar 2 20:48 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Mar 2 20:48 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Mar 2 17:43 ib_logfile1
-rw-r----- 1 mysql root 34661 Mar 2 20:48 master.err
-rw-rw---- 1 mysql mysql 5 Mar 2 20:48 master.pid
drwx------ 2 mysql mysql 4096 Mar 2 17:43 mysql
-rw-rw---- 1 mysql mysql 143 Mar 2 17:58 mysql-bin.000001
-rw-rw---- 1 mysql mysql 143 Mar 2 18:00 mysql-bin.000002
-rw-rw---- 1 mysql mysql 143 Mar 2 20:32 mysql-bin.000003
-rw-rw---- 1 mysql mysql 143 Mar 2 20:48 mysql-bin.000004
-rw-rw---- 1 mysql mysql 120 Mar 2 20:48 mysql-bin.000005
-rw-rw---- 1 mysql mysql 219 Mar 2 20:48 mysql-bin.index
drwx------ 2 mysql mysql 4096 Mar 2 17:43 performance_schema
drwxr-xr-x 2 mysql mysql 4096 Mar 2 17:36 test
[root@master data]#
Slave端重启，查看二进制日志文件(从服务器端二进制可打开也可不打开，不是必须要开)[root@slave data]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL..... SUCCESS!
[root@slave data]# ll
total 110632
-rw-rw---- 1 mysql mysql 56 Mar 2 20:31 auto.cnf
-rw-rw---- 1 mysql mysql 12582912 Mar 2 20:50 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Mar 2 20:50 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Mar 2 20:27 ib_logfile1
drwx------ 2 mysql mysql 4096 Mar 2 20:27 mysql
-rw-rw---- 1 mysql mysql 143 Mar 2 20:50 mysql-bin.000001
-rw-rw---- 1 mysql mysql 120 Mar 2 20:50 mysql-bin.000002
-rw-rw---- 1 mysql mysql 82 Mar 2 20:50 mysql-bin.index
drwx------ 2 mysql mysql 4096 Mar 2 20:27 performance_schema
-rw-r----- 1 mysql root 7650 Mar 2 20:50 slave.err
-rw-rw---- 1 mysql mysql 6 Mar 2 20:50 slave.pid
drwxr-xr-x 2 mysql mysql 4096 Mar 2 18:05 test
[root@slave data]#
或者通过如下方式查看[root@master data]# vim /etc/my.cnf
[root@master data]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.... SUCCESS!
[root@master data]# vim /etc/my.cnf
[root@master data]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.20-log Source distribution
Copyright (c) 2000, 2014, 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> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.15 sec)
mysql>
字符串log_bin状态为ON，表示二进制日志记录里功能已经开启。
这里遇到一个问题:
key_buffer_size = 8M、sort_buffer_size = 8M、interactive-timeout 参数在MySQL-5.6.20居然不生效，但是在5.5.32中不会出现不识别，是不是在MySQL-5.6中已经不支持者功能?我还没有找到原因...
六)主服务器添加授权和同步账户
grant replication slave on *.* to 'rep'@'10.0.0.%' identified by 'pwd123';[root@master data]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.20-log Source distribution
Copyright (c) 2000, 2014, 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> grant replication slave on *.* to 'rep'@'10.0.0.%' identified by 'pwd123';
Query OK, 0 rows affected (0.09 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.08 sec)
mysql> use mysql
Database changed
mysql>
mysql> select user,host from user where user='rep';
+------+----------+
| user | host |
+------+----------+
| rep | 10.0.0.% |
+------+----------+
1 row in set (0.00 sec)
mysql>
为了数据一致性和在同步之前再被写入，这里将主库添加锁功能mysql> flush tables with read lock;
Query OK, 0 rows affected (0.03 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 | 403 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
此时文件的备份点是403(其实就是二进制文件mysql-bin.000008的大小)和二进制文件为mysql-bin.000008,此时主库上的已经存在的二进制文件有:mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 143 |
| mysql-bin.000002 | 143 |
| mysql-bin.000003 | 143 |
| mysql-bin.000004 | 143 |
| mysql-bin.000005 | 143 |
| mysql-bin.000006 | 143 |
| mysql-bin.000007 | 143 |
| mysql-bin.000008 | 403 |
+------------------+-----------+
8 rows in set (0.01 sec)
mysql>
在主库上另外一打开一个窗口开始执行备份操作:[root@master ~]# mysqldump -uroot -p -A -B --events|gzip>/opt/rep.sql.gz
Enter password:
[root@master ~]# ll /opt/rep.sql.gz
-rw-r--r-- 1 root root 167424 Mar 2 21:26 /opt/rep.sql.gz
[root@master ~]#
将主库上备份数据导入从库，然后在主库上执行解锁功能，开启同步操作
[root@slave opt]# gunzip -c rep.sql.gz >rep.sql
[root@slave opt]# ll
total 764
-rw-r--r-- 1 root root 607764 Mar 2 21:36 rep.sql
-rw-r--r-- 1 root root 167424 Mar 2 21:26 rep.sql.gz
drwxr-xr-x. 2 root root 4096 Feb 22 2013 rh
[root@slave opt]#
[root@slave opt]# mysql -uroot -p
Enter password:
[root@slave opt]#
主库解开锁表功能
备份完成后需要对主库的表解锁，恢复对外提供服务mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql>
在从库上执行以下命令:
CHANGE MASTER TO
MASTER_HOST='10.0.0.7',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='pwd123',
MASTER_LOG_FILE='mysql-bin.000008',
MASTER_LOG_POS=403;
[root@slave opt]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.20-log Source distribution
Copyright (c) 2000, 2014, 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> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.7',
-> MASTER_PORT=3306,
-> MASTER_USER='rep',
-> MASTER_PASSWORD='pwd123',
-> MASTER_LOG_FILE='mysql-bin.000008',
-> MASTER_LOG_POS=403;
Query OK, 0 rows affected, 2 warnings (0.15 sec)
mysql>
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)
mysql>
以上语句信息会放在从库的master-info日志里面
进入从库data目录，打开master-info[root@slave ~]# cd /application/mysql/data/
[root@slave data]# ll
total 111260
-rw-rw---- 1 mysql mysql 56 Mar 2 20:31 auto.cnf
-rw-rw---- 1 mysql mysql 12582912 Mar 2 21:38 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Mar 2 21:38 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Mar 2 20:27 ib_logfile1
-rw-rw---- 1 mysql mysql 85 Mar 2 21:42 master.info
drwx------ 2 mysql mysql 4096 Mar 2 21:38 mysql
-rw-rw---- 1 mysql mysql 143 Mar 2 20:50 mysql-bin.000001
-rw-rw---- 1 mysql mysql 143 Mar 2 21:02 mysql-bin.000002
-rw-rw---- 1 mysql mysql 143 Mar 2 21:03 mysql-bin.000003
-rw-rw---- 1 mysql mysql 608233 Mar 2 21:43 mysql-bin.000004
-rw-rw---- 1 mysql mysql 164 Mar 2 21:03 mysql-bin.index
drwx------ 2 mysql mysql 4096 Mar 2 20:27 performance_schema
-rw-rw---- 1 mysql mysql 56 Mar 2 21:42 relay-log.info
-rw-r----- 1 mysql root 19466 Mar 2 21:42 slave.err
-rw-rw---- 1 mysql mysql 6 Mar 2 21:03 slave.pid
-rw-rw---- 1 mysql mysql 120 Mar 2 21:42 slave-relay-bin.000001
-rw-rw---- 1 mysql mysql 25 Mar 2 21:42 slave-relay-bin.index
drwxr-xr-x 2 mysql mysql 4096 Mar 2 18:05 test
[root@slave data]# cat master.info
23
mysql-bin.000008
403
10.10.0.7
rep
pwd123
3306
60
0
0
1800.000
0
86400
0
[root@slave data]#
在从库上开启同步开关:
mysql> start slave;
Query OK, 0 rows affected (0.07 sec)
mysql>
验证从库开关是否真的开启:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.7
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 403
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 283
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: 403
Relay_Log_Space: 456
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
Master_UUID: 9bb6cab9-e05b-11e5-8486-000c292fc28c
Master_Info_File: /application/mysql-5.6.20/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
根据显示结果查看:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
两个参数显示为Yes，则表明从库运行正常
七)验证数据同步
在主库上创建数据库测试库ucode，然后在从库上查看是否存在mysql> create database ucode;
Query OK, 1 row affected (0.07 sec)
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| ucode |
+--------------------+
5 rows in set (0.05 sec)
mysql>
从库查看是否存在ucode库:mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| ucode |
+--------------------+
5 rows in set (0.12 sec)
mysql>
在从库中继日志中查看是否有创建ucode语句，[root@slave data]# pwd
/application/mysql/data
[root@slave data]# ll
total 111288
-rw-rw---- 1 mysql mysql 56 Mar 2 20:31 auto.cnf
-rw-rw---- 1 mysql mysql 12582912 Mar 2 22:02 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Mar 2 22:02 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Mar 2 20:27 ib_logfile1
-rw-rw---- 1 mysql mysql 120 Mar 2 22:14 master.info
drwx------ 2 mysql mysql 4096 Mar 2 21:38 mysql
-rw-rw---- 1 mysql mysql 143 Mar 2 20:50 mysql-bin.000001
-rw-rw---- 1 mysql mysql 143 Mar 2 21:02 mysql-bin.000002
-rw-rw---- 1 mysql mysql 143 Mar 2 21:03 mysql-bin.000003
-rw-rw---- 1 mysql mysql 608256 Mar 2 22:02 mysql-bin.000004
-rw-rw---- 1 mysql mysql 120 Mar 2 22:02 mysql-bin.000005
-rw-rw---- 1 mysql mysql 205 Mar 2 22:02 mysql-bin.index
drwx------ 2 mysql mysql 4096 Mar 2 20:27 performance_schema
-rw-rw---- 1 mysql mysql 58 Mar 2 22:14 relay-log.info
-rw-r----- 1 mysql root 29323 Mar 2 22:07 slave.err
-rw-rw---- 1 mysql mysql 6 Mar 2 22:02 slave.pid
-rw-rw---- 1 mysql mysql 173 Mar 2 22:07 slave-relay-bin.000001
-rw-rw---- 1 mysql mysql 380 Mar 2 22:14 slave-relay-bin.000002
-rw-rw---- 1 mysql mysql 50 Mar 2 22:07 slave-relay-bin.index
drwxr-xr-x 2 mysql mysql 4096 Mar 2 18:05 test
drwx------ 2 mysql mysql 4096 Mar 2 22:14 ucode
[root@slave data]# mysqlbinlog slave-relay-bin.000002 |grep "create database ucode"
create database ucode
[root@slave data]#
由以上查看说明从库上存在ucode库创建语句，至此，数据主从同步功能实现；
补充知识:
从主库备份数据的时候如果运用--master-data=1参数，即:
mysqldump -uroot -p -A -B --events --master-data=1 >/opt/rep.sql
然后再把rep.sql语句往从库导数据的时候将这库导进去，接着在从库上执行CHANGE MASTER TO语句时，丢掉下面红色部分语句:
CHANGE MASTER TO
MASTER_HOST='10.0.0.7',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='pwd123',
MASTER_LOG_FILE='mysql-bin.000008',
MASTER_LOG_POS=403;
提示：--master-data=2时:
语句“CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=403”被注释掉；
提示: --master-data=1时:
语句:"CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=403"未被注释所以在--master-data=1时候，将rep.sql导入从库收，再执行：
CHANGE MASTER TO
MASTER_HOST='10.0.0.7',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='oldboy123',
语句是可以成功的，因为事先已经将：MASTER_LOG_FILE='mysql-bin.000008',MASTER_LOG_POS=403;
语句已经导入到库中,后面不用在导入“MASTER_LOG_FILE和MASTER_LOG_POS”参数
即:--master-data=1在做主从复制的时候已经帮我们找好了同步的文件名和位置，所以以后做主从同步复制的时候可以不用执行锁表(flush table with read lock)操作
八)生产环境中MySQL主从复制读写分离授权方案讲解
当配置好MySQL主从复制后，对数据库内容的所有更新必须在主库上进行，那么为什么所有的更新都要在主服务器上进行呢？这是主要是原因是MySQL的主从复制是单向进行的，也就是说数据只能从主库向从库更新，不能逆向进行，试想如果同时可以在主从库上进行写入数据，两边数据就会不一致，从而会引起冲突，所以我们对数据的写入正确可靠的顺序应该是主库写入，从库读取(当然主库也可以承担读的任务，一般不这样做。)
那如何来控制用户只能在主上更新呢？
常见的方法有:
a)在从库上对连接用户进行授权控制，即对某个用户连接过来只能进行查询操作和可执行操作，即只读操作
b)web程序写的时候直接指向主库，而读的时候指向从库
c)让从库只能读不能写
生产环境MySQL主从复制用户授权方案介绍
以为ucode数据库为例
方案1：
主库:设置访问主库ucode的用户为web，权限为:select、insert、delete、update
从库:访问主库的用户web同步到从库，然后回收insert、delete、update权限，再设置read-only参数确保从库只读
对开发而言，程序在主库上只能进行select、insert、delete、update操作，在从库上只能进行select操作
方案2:
分别给访问主库和从库不同的用户，比如ucode_w是访问主库的用户，ucode_r,对应权限仍然是:
主库:ucode_w(select、insert、delete、update)，
从库:ucode_r(select)
但是这样存在一定的风险，ucode_w 连接从库，所以必须在从库上设置read-only参数，确保从库只读
对开发而言，由于有两套用户密码，不够专业
方案3:
通过忽略授权表方式防止数据写从库，然后对从服务器(slave)上的用户仅授权select读权限。不同步系统库mysql，这样我们就保证主库和从库相同的用户可以授权不同的权限，具体操作是:
忽略系统库mysql库和information_schema库的主从同步，(比如不同步系统库mysql)在主从库进行如下授权
主:ucode select、insert、delete、update
从:select
比如忽略mysql和infomation_schema库的主从同步操作
replicate-ignore-db=mysql
binlog-do-db =ucode
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema
提示:忽略binlog日志的参数binlog-ignore-db一般用于系统的库和表
缺点:如果从库切换主的时候，连接用户权限问题。解决方法:预留一个从库专门用来接替主库
MySQL默认是所有库都同步，第三种方式其实就是忽略部分库，即只同步部分库，由于系统库是控制用户和用户权限的，如果系统库不同步，那么主从库上分别创建用户指定对应的权限，即使用户名和密码相同，登录不同的数据库后，对数据的操作方式也不会不同，(即从库上只读操作主库上读写操作)
解决日志刷新到磁盘问题
主库:
为了保证主库上每次提交事务前将二进制日志同步到磁盘上，保证在服务器崩溃时不发送数据丢失事件，可以在配置文件中设置如下参数:
sync_binlog = 1
从库:
为了保证从库上的数据能定时的刷新到磁盘上，在配置文件中增加如下选项:
sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info = 1
解释:
sync_relay_log = 1这个参数和sync_binlog是一样的，当设置为1时，slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区，然后刷入relay log中继日志里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量I/O。当设置为0时，并不是马上就刷入中继日志里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘I/O操作。这个值默认是0，可动态修改，建议采用默认值。
1307
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
