MYSQL主从复制和读写分享配置

1、MYSQL主从复制和读写分享配置创建时间：2016/4/15 星期五 下午 3:41更新时间：2016/4/18 星期一 下午 4:52作者：pengguangzhi2006  一、准备工作：（1）配置MySQL主从复制（读写分离）之前，需要在主从两台服务器先安装好MySQL5.6。（2）目前最新的MySQL5.6 GA版本是MySQL5.6.12。个人推荐Linux（RedHat/CentOS 6.4）源码编译安装，具体可以看本站这篇教程：RedHat/CentOS源码编译安装MySQL5.6.12（3）注意：（a）如果你需要用于生产环境，安教程安装MySQL时不要急着做mysql

2、启动操作。建议把mysql初始化生成的/usr/local/mysql/f删除，然后把你优化好的mysql配置文件f放到/etc下。（b）建议主备两台服务器在同一局域网，主备两台数据库网络需要互通。（4）我的环境：主数据库IP：14从数据库IP：23  二、 修改主数据库的的配置文件：mysqldserver-id=1log-bin=mysqlmaster-bin.logsync_binlog=1#注意：下面这个参数需要修改为服务器内存的70%左右innodb_buffer_pool_size=700Minnodb_flush_log_at

3、_trx_commit=1sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZEROlower_case_table_names=1log_bin_trust_function_creators=1-（1）server-id=1用于配置服务器ID,是mysql主从集群中的一个唯一区分号。所有的服务器都应该有自己的唯一ID,如果一个Slave连接到Master，但是和Master有相同的ID,则会产生Master和Slave ID相同的错误（2）binlog-do-db

4、 ：指定主从复制的数据库。（3）log-bin字段给出了二进制日志所产生的所有文件的基本名，二进制文件可以包含多个文件:（4）log-bin-index.这其实是指定了一个索引文件（纯文本，内容为文件列表），该索引文件中包含所有二进制文件的列表。 如果没有为log-bin-index设置默认值，则会使用机器的hostname产生log-bin-index文件，这样，在服务器的 hostname改变后，可能会出现无法找到索引文件，从而认为二进制文件列表为空，导致无法正确的生成二进制bin-log。所以，推荐应该使用机器无关的名字作为bin-log-index的文件名.(可以不填，会自动生成)（5

5、）sync_binlog=1 MySQL服务器将它的二进制日志同步到硬盘上； 默认值是0，不与硬盘同步。值为1是最安全的选择，因为崩溃时，你最多丢掉二进制日志中的一个语句/事务；但是，这是最慢的选择(除非硬盘有电池备份缓存，从而使同步工作较快)。- 修改之后要重启mysql：/etc/init.d/mysql restart三、修改从数据库的的配置文件（server-id配置为大于1的数字即可）：server-id=2log-bin=mysqlslave-bin.logsync_binlog=1#注意：下面这个参数需要修改为服务器内存的70%左右innodb_buffer_

6、pool_size=700Minnodb_flush_log_at_trx_commit=1sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZEROlower_case_table_names=1log_bin_trust_function_creators=1-以下为参数解释：innodb_buffer_pool_size 这个参数主要作用是这个参数主要作用是 缓存innodb表的索引，数据，插入数据时的缓冲默认值：128M； 专用mysql服务器设置的

7、大小： 操作系统内存的70%-80%最佳。此外，这个参数是非动态的，要修改这个值，需要重启mysqld服务。所以设置的时候要非常谨慎。并不是设置的越大越好。设置的过大，会导致system的swap空间被占用，导致操作系统变慢，从而减低sql查询的效率。如 果用Innodb，那么这是一个重要变量。相对于MyISAM来说，Innodb对于buffer size更敏感。MySIAM可能对于大数据量使用默认的key_buffer_size也还好，但Innodb在大数据量时用默认值就感觉在爬了。 Innodb的缓冲池会缓存数据和索引，所以不需要给系统的缓存留空间，如果只用Innodb，可以把这个值设为内

8、存的70%-80%。和 key_buffer相同，如果数据量比较小也不怎么增加，那么不要把这个值设太高也可以提高内存的使用率。innodb_flush_log_at_trx_commit  （这个很管用） 抱 怨Innodb比MyISAM慢 100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电 池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬

9、盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统 挂了时才可能丢数据。innodb_additional_pool_size 这个的效果不是很明显，至少是当操作系统能合理分配内存时。但你可能仍需要设成20M或更多一点以看Innodb会分配多少内存做其他用途。innodb_log_file_size对于写很多尤其是大数据量时非常重要。要注意，大的文件提供更高的性能，但数据库恢复时会用更多的时间。我一般用64M-512M，具体取决于服务器的空间。lower_case_table_names=1

10、 这样MySQL 将在创建与查找时将所有的表名自动转换为小写字符当你更改这个选项时，你必须在启动 mysqld 前首先将老的表名转换为小写字母。换句话说，如果你希望在数据库里面创建表的时候保留大小写字符状态，则应该把这个参数置0： lower_case_table_names=0 。否则的话你会发现同样的sqldump脚本在不同的操作系统下最终导入的结果不一样（在Windows下所有的大写字符都变成小写了）。log_bin_trust_function_creators=1 If set to 0 (the default), users are not

11、 permitted to create or alter stored functions unless they have the SUPER privilege in addition to the CREATE ROUTINE or ALTER ROUTINE privilege. A setting of 0 also enforces the restriction that a function must be declared with the DETERMINISTIC characteristic, or with the READS SQL DATA or NO SQL

12、characteristic. If the variable is set to 1, MySQL does not enforce these restrictions on stored function creation. This variable also applies to trigger creation.当有mysql本地或远程建立function或procedure时报上面的错误经试验是log_bin_trust_function_creators值为off导致sql_mode 定义了mysql应该支持的sql语法，数据校验等！mysql>

13、60;select sql_mode;mysql5.0以上版本支持三种sql_mode模式：ANSI、TRADITIONAL和STRICT_TRANS_TABLES。ANSI模式：                              宽松模式，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告。TRADITIONAL模式：          

14、;     严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。用于事物时，会进行事物的回滚。STRICT_TRANS_TABLES模式：严格模式，进行数据的严格校验，错误数据不能插入，报error错误。NO_AUTO_VALUE_ON_ZERO 影响AUTO_INCREMENT列的处理。 一般情况，你可以向该列插入NULL或0生成下一个序列号。 NO_AUTO_VALUE_ON_ZERO禁用0，因此只有NULL可以生成下一个序列号。NO_AUTO_CREATE_USEROne of Server SQL Modes,to

15、prevent the GRANT statement from automatically creating new users if it would otherwise do so, unless a nonempty password also is specified.如果在mysql客户端中运行：mysql> set sql_mode = 'no_auto_create_user'那么，上述的语句将不能成功，原因是没有提供用户密码，解决的办法是添加一个indentified 子句，例如：grant CREATE, DROP,INSERT, SELECT, DE

16、LETE, UPDATE,ALTER on xxx.* to xxx identified by 'xxx'另外，要注意的是，空密码是不认的，例如：grant CREATE, DROP,INSERT, SELECT, DELETE, UPDATE,ALTER on xxx.* to xxx identified by '' 就不行。NO_ENGINE_SUBSTITUTION如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常+mysql的sql_mode合理设置sql_mode是个很容易被忽视的变量，默认值是空值，

17、在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式，所以开发、测试环境的数据库也必须要设置，这样在开发测试阶段就可以发现问题 sql_mode常用值如下： ONLY_FULL_GROUP_BY：对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中NO_AUTO_VALUE_ON_ZERO：该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。STRICT_TRA

18、NS_TABLES：在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制NO_ZERO_IN_DATE：在严格模式下，不允许日期和月份为零NO_ZERO_DATE：设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。ERROR_FOR_DIVISION_BY_ZERO：在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如 果未给出该模式，那么数据被零除时MySQL返回NULLNO_AUTO_CREATE_USER：禁止GRANT创建密码为空的用户NO_ENGINE_SUBSTITUTION：如果需要的存储引擎被禁用或未

19、编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常PIPES_AS_CONCAT：将"|"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似ANSI_QUOTES：启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符ORACLE的sql_mode设置等同：PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_U

20、SER. 如果使用mysql，为了继续保留大家使用oracle的习惯，可以对mysql的sql_mode设置如下：在f添加如下配置mysqldsql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES'- 修改之后要重启mysql：/et

21、c/init.d/mysql restart 四、SSH登录到主数据库：（1）在主数据库上创建用于主从复制的账户（23换成你的从数据库IP）：1# mysql -uroot -p2mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl''23' IDENTIFIED BY 'repl'Create user reply_user;Grant REPLICATION SLAVE On *.* to reply_user IDENTIFIED BY xx

22、xxxxxxx;Flush privileges;-mysql 新设置用户或更改密码后需用flush privileges刷新MySQL的系统权限相关表,否则会出现拒绝访问mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl''192.168.0.%' IDENTIFIED BY 'repl'其中要特别说明一下192.168.0.%，这个配置是指明repl用户所在服务器， 这里%是通配符，表示-55的Server都可以以repl用户登陆主服务器。如果没有使用

23、通配符，而访问 的服务器又不在上述配制里，那么你将无法使用该账户从你的服务器replicate主服务器.（2）主数据库锁表（禁止再插入数据以获取主数据库的的二进制日志坐标）：1mysql> FLUSH TABLES WITH READ LOCK;（3）然后克隆一个SSH会话窗口，在这个窗口打开MySQL命令行：1# mysql -uroot -p2mysql> SHOW MASTER STATUS;3+-+-+-+-+-+4| File            &#

24、160;      | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |5+-+-+-+-+-+6| mysqlmaster-bin.000001 |      330 |              |       &#

25、160;          |                   |7+-+-+-+-+-+81 row in set (0.00 sec)9mysql> exit;在这个例子中，二进制日志文件是mysqlmaster-bin.000001，位置是330，记录下这两个值，稍后要用到。（4）在主数据库上使用mysqldum

26、p命令创建一个数据快照，即备份，导出所有库：1#mysqldump -uroot -p -h -P3306 -all-databases  -triggers -routines -events >all.sql2# 接下来会提示你输入mysql数据库的root密码，输入完成后，如果当前数据库不大，很快就能导出完成。（5）解锁第（2）步主数据的锁表操作：1mysql> UNLOCK TABLES;五、SSH登录到从数据库：（1）通过FTP、SFTP或其他方式，将上一步备份的主数据库快照all.sql上传到从数据库某个路径，例如我放在了/home/yimi

27、ju/目录下；（2）导入主的快照到从：1# cd /home/yimiju2# mysql -uroot -p -h -P3306 < all.sql3# 接下来会提示你输入mysql数据库的root密码，输入完成后，如果当前数据库不大，很快就能导入完成。 或者mysql命令行mysql>source all.sql;（3）给从数据库设置复制的主数据库信息（注意修改MASTER_LOG_FILE和MASTER_LOG_POS的值）：1# mysql -uroot -p2mysql> CHANGE MASTER TO MASTER_HOST='192.

28、168.2.114', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_LOG_FILE='mysqlmaster-bin.000001',MASTER_LOG_POS=330;3# 然后启动从数据库的复制线程：4mysql> START slave;5# 接着查询数据库的slave状态：6mysql>  SHOW slave STATUS G7# 如果下面两个参数都是Yes，则说明主从配置成功！8Slave_IO_Running: Yes9Slave_SQL_R

29、unning: Yes  如果是Slave_IO_State一项显示：Waiting for master to send event，表示所有工作已经就绪。（4）接下来你可以在主数据库上创建数据库、表、插入数据，然后看从数据库是否同步了这些操作。mysql> CREATE TABLE web_user2 (      id int(10) unsigned NOT NULL AUTO_INCREMENT,      name varchar(30) NOT NULL DEFAULT '&#

30、39;,     creatime datetime NOT NULL DEFAULT '0000-00-00 00:00:00',     PRIMARY KEY (id)      ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;Query OK, 0 rows affected (1.98 sec)mysql> show tables;+-+| Tables_in_test |+-+| web_user2      |+-+1 row in set (0.00 sec)mysql> Insert into web_user2 (name