mysql5.6主从环境安装配置步骤详解

主从配置是现在服务器集群中一个重要的数据处理方式了,下文来介绍在mysql5.6中快速配置安装mysql5.6的主从配置了,希望能帮助到各位.

服务器配置:

10.1.1.231 master server

10.1.1.234 slave server

服务器系统为:centos5.6

软件包:

1. MySQL-client-5.6.13-1.linux_glibc2.5.x86_64.rpm
3. MySQL-devel-5.6.13-1.linux_glibc2.5.x86_64.rpm
5. MySQL-server-5.6.13-1.linux_glibc2.5.x86_64.rpm
7. MySQL-shared-5.6.13-1.linux_glibc2.5.x86_64.rpm
9. MySQL-shared-compat-5.6.13-1.linux_glibc2.5.x86_64.rpm
11. MySQL-test-5.6.13-1.linux_glibc2.5.x86_64.rpm

一、安装mysql

1. yum-yinstalllibaiolibaio-develperl-Time-HiRescurlcurl-develzlib-developenssl-develperlcpioexpat-develgettext-develperl-ExtUtils-MakeMakerperl-DBD-MySQL
2. 　
3. rpm-ivhMySQL-test-5.6.13-1.linux_glibc2.5.x86_64.rpm
4. rpm-ivhMySQL-shared-5.6.13-1.linux_glibc2.5.x86_64.rpm
5. rpm-ivhMySQL-shared-compat-5.6.13-1.linux_glibc2.5.x86_64.rpm
6. rpm-ivhMySQL-server-5.6.13-1.linux_glibc2.5.x86_64.rpm
7. rpm-ivhMySQL-devel-5.6.13-1.linux_glibc2.5.x86_64.rpm
8. rpm-ivhMySQL-client-5.6.13-1.linux_glibc2.5.x86_64.rpm

二、修改master和slave配置,部分参数要根据实际情况来调整,主库/etc/my.cnf配置:

1. [client]
2. port=3306
3. socket=/state/partition1/mysql/mysql.sock
4. default-character-set=utf8
5. [mysqld]
6. server-id=1025
7. log-bin=mysql-master-bin
8. binlog_format=mixed
9. expire_logs_days=15
10. max_connections=1000
11. innodb_flush_log_at_trx_commit=1
12. sync_binlog=1
13. binlog-do-db=denovo_ng
14. binlog-ignore-db=mysql,test,information_schema
15. innodb_buffer_pool_size=46673M
16. skip-name-resolve
17. datadir=/state/partition1/mysql/data
18. port=3306
19. socket=/state/partition1/mysql/mysql.sock
20. key_buffer_size=16M
21. max_allowed_packet=16M
22. join_buffer_size=512M
23. sort_buffer_size=256M
24. read_rnd_buffer_size=128M
25. innodb_buffer_pool_size=40960M
26. sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

从库/etc/my.cnf配置:

1. [client]
2. port=3306
3. socket=/state/partition1/mysql/mysql.sock
4. [mysqld]
5. server-id=1052
6. datadir=/state/partition1/mysql/data
7. port=3306
8. socket=/state/partition1/mysql/mysql.sock
9. user=mysql
10. log-bin=mysql-slave-bin
11. max_binlog_size=1000M
12. binlog_format=mixed
13. expire_logs_days=7
14. innodb_flush_log_at_trx_commit=1
15. sync_binlog=1
16. read_only=1
17. binlog-do-db=denovo_ng
18. binlog-ignore-db=mysql,test,information_schema
19. innodb_buffer_pool_size=36673M
20. skip-name-resolve
21. max_connections=1000
22. max_user_connections=490
23. max_connect_errors=2
24. key_buffer_size=16M
25. max_allowed_packet=16M
26. join_buffer_size=512M
27. sort_buffer_size=256M
28. read_rnd_buffer_size=128M
29. innodb_buffer_pool_size=40960M--phpfensi.com
30. sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

三、进行主从库账号授权:

主库:grant replication slave on *.* to 'jpsync'@'10.1.1.234' identified by 'jppasswd';

flush privileges;

show master status; #查看主库信息

从库:

1. stopslave;
2. changemastertomaster_host='10.1.1.231',master_port=3306,master_user='jpsync',master_password='jppasswd',master_log_file='mysql-master-bin.000003',master_log_pos=408;
3. startslave；
4. showslavestatusG;

如果出现:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

表明mysql主从库成功.