当前位置：首页 > 资讯 > 技术文档

MySQL5.6 Replication主从复制（读写分离) 配置完整版

时间：2021-01-21 19:42 编辑：来源：阅读：
扫一扫，手机访问

摘要：MySQL5.6 Replication主从复制（读写分离) 配置完整版

MySQL5.6主从复制(读写分离)教程 [b]1、MySQL5.6开始主从复制有两种方式：[/b] 基于日志（binlog）；基于GTID（全局事务标示符）。需要注意的是：GTID方式不支持临时表！所以如果你的业务系统要用到临时表的话就不要考虑这种方式了，至少目前最新版本MySQL5.6.12的GTID复制还是不支持临时表的。所以本教程主要是告诉大家如何通过日志（binlog）方式做主从复制！ [b]2、MySQL官方提供的MySQL Replication教程：[/b] [url=http://dev.mysql.com/doc/refman/5.6/en/replication.html]http://dev.mysql.com/doc/refman/5.6/en/replication.html[/url] [b]第一步：准备工作[/b] 主服务器: 192.168.1.100 从服务器: 192.168.1.101 MySQL软件版本： MySQL-server-advanced-5.6.18-1.el6.x86_64.rpm MySQL-cient-advanced-5.6.18-1.el6.x86_64.rpm [b]第二步：在主服务器和从服务器上安装MySQL数据库软件[/b] 安装方法，请参见 [url=http://www.1sucai.cn/article/82542.htm]http://www.1sucai.cn/article/82542.htm[/url] MySQL数据库软件安装完成后，不要急着做mysql启动操作。建议把mysql初始化生成的/usr/my.cnf (如果是从源文件编译安装时，路径应该是在/usr/local/mysql/mysql.cnf)删除，然后把优化好的mysql 配置文件my.cnf放到/etc下。第三步：修改主数据库的配置文件/usr/my.cnf

[url=http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html]http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html[/url] [client] port=3306 socket=/usr/local/mysql/mysql.sock default-character-set=utf8 [mysqld] sync_binlog=1 server-id=1 port=3306 socket=/usr/local/mysql/mysql.sock pid-file=/home/mysql/temp/my3306.pid user=mysql datadir=/home/mysql/data tmpdir=/home/mysql/temp/ log-bin=/home/mysql/data/mysqlmaster-bin log-error=/home/mysql/logs/error.log slow_query_log_file=/home/mysql/logs/slow.log binlog_format=mixed slow_query_log long_query_time=10 wait_timeout=31536000 interactive_timeout=31536000 max_connections=500 max_user_connections=490 max_connect_errors=2 character_set_server=utf8 skip-external-locking key_buffer_size = 128M max_allowed_packet = 5M table_open_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 binlog-ignore-db=mysql binlog-ignore-db=information_schema replicate_ignore_db=mysql replicate_ignore_db=information_schema expire-logs-days=10 skip-slave-start skip-name-resolve lower_case_table_names=1 log_bin_trust_function_creators=1 # InnoDB innodb_data_home_dir=/home/mysql/data innodb_log_group_home_dir=/home/mysql/logs innodb_data_file_path=ibdata1:128M:autoextend innodb_buffer_pool_size=2G innodb_log_file_size=10M innodb_log_buffer_size=8M innodb_lock_wait_timeout=50 innodb_file_per_table innodb_flush_log_at_trx_commit=1 #sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

一个优化好的从数据库的配置文件如下：

[url=http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html]http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html[/url] [client] port=3306 socket=/usr/local/mysql/mysql.sock default-character-set=utf8 [mysqld] sync_binlog=1 server-id=2 port=3306 socket=/usr/local/mysql/mysql.sock pid-file=/home/mysql/temp/my3306.pid user=mysql datadir=/home/mysql/data tmpdir=/home/mysql/temp/ log-bin=/home/mysql/data/mysqlslave-bin log-error=/home/mysql/logs/error.log slow_query_log_file=/home/mysql/logs/slow.log binlog_format=mixed slow_query_log long_query_time=10 wait_timeout=31536000 interactive_timeout=31536000 max_connections=500 max_user_connections=490 max_connect_errors=2 character_set_server=utf8 skip-external-locking key_buffer_size = 128M max_allowed_packet = 5M table_open_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 binlog-ignore-db=mysql binlog-ignore-db=information_schema replicate_ignore_db=mysql replicate_ignore_db=information_schema expire-logs-days=10 #skip-slave-start skip-name-resolve lower_case_table_names=1 log_bin_trust_function_creators=1 # InnoDB innodb_data_home_dir=/home/mysql/data innodb_log_group_home_dir=/home/mysql/logs innodb_data_file_path=ibdata1:128M:autoextend innodb_buffer_pool_size=2G innodb_log_file_size=10M innodb_log_buffer_size=8M innodb_lock_wait_timeout=50 innodb_file_per_table innodb_flush_log_at_trx_commit=1 #sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 256K sort_buffer_size = 256K read_buffer = 256K write_buffer = 256K [mysqlhotcopy] interactive-timeout sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 256K sort_buffer_size = 256K read_buffer = 256K write_buffer = 256K [mysqlhotcopy] interactive-timeout

全部评论(0)

上一篇：Mysql使用简单教程(二)
下一篇：MySQL中Stmt 预处理提高效率问题的小研究

资讯排行榜
更多>>