今天分给各位分享两篇centos5.6中mysql主从配置相关教程了,在这里我们先来介绍mysql5.6主从环境配置安装方法,希望能帮助到大家。
mysql5.6主从环境配置安装
服务器配置:
10.1.1.231 master server
10.1.1.234 slave server
服务器系统为:centos5.6
软件包:
MySQL-client-5.6.13-1.linux_glibc2.5.x86_64.rpm
MySQL-devel-5.6.13-1.linux_glibc2.5.x86_64.rpm
MySQL-server-5.6.13-1.linux_glibc2.5.x86_64.rpm
MySQL-shared-5.6.13-1.linux_glibc2.5.x86_64.rpm
MySQL-shared-compat-5.6.13-1.linux_glibc2.5.x86_64.rpm
MySQL-test-5.6.13-1.linux_glibc2.5.x86_64.rpm
一、安装mysql
代码如下 |
|
yum -y install libaio libaio-devel perl-Time-HiRes curl curl-devel zlib-devel openssl-devel perl cpio expat-devel gettext-devel perl-ExtUtils-MakeMaker perl-DBD-MySQL
rpm -ivh MySQL-test-5.6.13-1.linux_glibc2.5.x86_64.rpm
rpm -ivh MySQL-shared-5.6.13-1.linux_glibc2.5.x86_64.rpm
rpm -ivh MySQL-shared-compat-5.6.13-1.linux_glibc2.5.x86_64.rpm
rpm -ivh MySQL-server-5.6.13-1.linux_glibc2.5.x86_64.rpm
rpm -ivh MySQL-devel-5.6.13-1.linux_glibc2.5.x86_64.rpm
rpm -ivh MySQL-client-5.6.13-1.linux_glibc2.5.x86_64.rpm
|
二、修改master和slave配置(部分参数要根据实际情况来调整):
主库/etc/my.cnf配置:
代码如下 |
|
[client]
port = 3306
socket = /state/partition1/mysql/mysql.sock
default-character-set=utf8
[mysqld]
server-id=1025
log-bin=mysql-master-bin
binlog_format = mixed
expire_logs_days=15
max_connections=1000
innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog-do-db=denovo_ng
binlog-ignore-db=mysql,test,information_schema
innodb_buffer_pool_size = 46673M
skip-name-resolve
datadir = /state/partition1/mysql/data
port = 3306
socket = /state/partition1/mysql/mysql.sock
key_buffer_size=16M
max_allowed_packet=16M
join_buffer_size = 512M
sort_buffer_size = 256M
read_rnd_buffer_size = 128M
innodb_buffer_pool_size = 40960M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
|
从库/etc/my.cnf配置:
代码如下 |
|
[client]
port = 3306
socket = /state/partition1/mysql/mysql.sock
[mysqld]
server-id=1052
datadir = /state/partition1/mysql/data
port = 3306
socket = /state/partition1/mysql/mysql.sock
user=mysql
log-bin=mysql-slave-bin
max_binlog_size=1000M
binlog_format = mixed
expire_logs_days=7
innodb_flush_log_at_trx_commit=1
sync_binlog=1
read_only=1
binlog-do-db=denovo_ng
binlog-ignore-db=mysql,test,information_schema
innodb_buffer_pool_size = 36673M
skip-name-resolve
max_connections=1000
max_user_connections=490
max_connect_errors=2
key_buffer_size=16M
max_allowed_packet=16M
join_buffer_size = 512M
sort_buffer_size = 256M
read_rnd_buffer_size = 128M
innodb_buffer_pool_size = 40960M
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; #查看主库信息
从库:
stop slave;
change master to master_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;
start slave;
show slave statusG;
如果出现:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
表明mysql主从库成功。
|
接着我们再来看MHA实现mysql5.6主从切换之安装配置
基于这里的master/slave复制,我们这里介绍下MHA软件的安装与测试
一、安装MHA软件:
代码如下 |
|
yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager
wget http://mysql-master-ha.googlecode.com/files/mha4mysql-node-0.52-0.noarch.rpm
wget https://mysql-master-ha.googlecode.com/files/mha4mysql-manager-0.52-0.noarch.rpm
wget https://mysql-master-ha.googlecode.com/files/mha4mysql-manager-0.52.tar.gz
rpm包安装:
rpm -ivh mha4mysql-manager-0.52-0.noarch.rpm
rpm -ivh mha4mysql-node-0.52-0.noarch.rpm
源码包安装(我这里采用源码包安装,manager安装在slave上)
tar zxvf mha4mysql-manager-0.52.tar.gz
cd mha4mysql-manager-0.52
perl Makefile.PL
make
make install
|
二、配置基本环境:
1、配置master和slave之间可以ssh无密码登陆(这里略)
2、保证mha manager能登陆主从服务器的mysql
10.1.1.231上执行:
代码如下 |
|
grant all privileges on *.* to 'root'@'10.1.1.234' identified by 'rootpasswd';
flush privileges;
10.1.1.234上执行:
grant all privileges on *.* to 'root'@'10.1.1.231' identified by 'rootpasswd';
flush privileges;
|
三、修改mha的manager配置文件:
代码如下 |
|
[root@localhost mha4mysql-manager-0.52]#mkdir /etc/masterha
[root@localhost mha4mysql-manager-0.52]#mkdir -p /masterha/app1
[root@localhost mha4mysql-manager-0.52]#cp samples/conf/* /etc/masterha/
[root@localhost mha4mysql-manager-0.52]#cat /etc/masterha/app1.cnf
[root@localhost mha4mysql-manager-0.52]#mkdir /etc/masterha
[root@localhost mha4mysql-manager-0.52]#mkdir -p /masterha/app1
[root@localhost mha4mysql-manager-0.52]#cp samples/conf/* /etc/masterha/
[root@localhost mha4mysql-manager-0.52]#cat /etc/masterha/app1.cnf
[server default]
manager_workdir=/masterha/app1/
manager_log=/masterha/app1/manager.log
user=root
password=d3n0v0
ssh_user=root
repl_user=jpsync
repl_password=jpsyncpass2014la
ping_interval=1
shutdown_script=""
#master_ip_failover_script="/usr/local/bin/master_ip_failover"
master_ip_online_change_script=""
report_script=""
[server1]
hostname=10.1.1.231
port=63306
master_binlog_dir="/state/partition1/mysql/data"
candidate_master=1
[server2]
hostname=10.1.1.234
port=63306
master_binlog_dir="/state/partition1/mysql/data"
candidate_master=1
|
四、检测配置:
检查ssh:
代码如下 |
|
[root@localhost masterha]# masterha_check_ssh --conf=/etc/masterha/app1.cnf
Fri Sep 12 15:23:25 2014 - [info] Reading default configuratoins from /etc/masterha_default.cnf..
Fri Sep 12 15:23:25 2014 - [info] Reading application default configurations from /etc/masterha/app1.cnf..
Fri Sep 12 15:23:25 2014 - [info] Reading server configurations from /etc/masterha/app1.cnf..
Fri Sep 12 15:23:25 2014 - [info] Starting SSH connection tests..
Fri Sep 12 15:23:32 2014 - [debug]
Fri Sep 12 15:23:25 2014 - [debug] Connecting via SSH from root@10.1.1.231(10.1.1.231) to root@10.1.1.234(10.1.1.234)..
Warning: untrusted X11 forwarding setup failed: xauth key data not generated
Warning: No xauth data; using fake authentication data for X11 forwarding.
Fri Sep 12 15:23:32 2014 - [debug] ok.
Fri Sep 12 15:23:38 2014 - [debug]
Fri Sep 12 15:23:26 2014 - [debug] Connecting via SSH from root@10.1.1.234(10.1.1.234) to root@10.1.1.231(10.1.1.231)..
Warning: untrusted X11 forwarding setup failed: xauth key data not generated
Warning: No xauth data; using fake authentication data for X11 forwarding.
Warning: untrusted X11 forwarding setup failed: xauth key data not generated
Warning: No xauth data; using fake authentication data for X11 forwarding.
Fri Sep 12 15:23:38 2014 - [debug] ok.
Fri Sep 12 15:23:38 2014 - [info] All SSH connection tests passed successfully.
|
检查mha启动状态:
代码如下 |
|
[root@localhost masterha]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:11444) is running(0:PING_OK), master:10.1.1.231
|
检查主从复制状态:
代码如下 |
|
[root@compute-0-52 masterha]# masterha_check_repl --conf=/etc/masterha/app1.cnf
Fri Sep 12 16:15:12 2014 - [info] Reading default configuratoins from /etc/masterha_default.cnf..
Fri Sep 12 16:15:12 2014 - [info] Reading application default configurations from /etc/masterha/app1.cnf..
Fri Sep 12 16:15:12 2014 - [info] Reading server configurations from /etc/masterha/app1.cnf..
Fri Sep 12 16:15:12 2014 - [info] MHA::MasterMonitor version 0.52.
Fri Sep 12 16:15:12 2014 - [info] Dead Servers:
Fri Sep 12 16:15:12 2014 - [info] Alive Servers:
Fri Sep 12 16:15:12 2014 - [info] 10.1.1.231(10.1.1.231:3306)
Fri Sep 12 16:15:12 2014 - [info] 10.1.1.234(10.1.1.234:3306)
Fri Sep 12 16:15:12 2014 - [info] Alive Slaves:
Fri Sep 12 16:15:12 2014 - [info] 10.1.1.234(10.1.1.234:3306) Version=5.6.13-log (oldest major version between slaves) log-bin:enabled
Fri Sep 12 16:15:12 2014 - [info] Replicating from 10.1.1.231(10.1.1.231:3306)
Fri Sep 12 16:15:12 2014 - [info] Primary candidate for the new Master (candidate_master is set)
Fri Sep 12 16:15:12 2014 - [info] Current Alive Master: 10.1.1.231(10.1.1.231:3306)
Fri Sep 12 16:15:12 2014 - [info] Checking slave configurations..
Fri Sep 12 16:15:12 2014 - <div class="warning shortcodestyle"></div> relay_log_purge=0 is not set on slave 10.1.1.234(10.1.1.234:3306).
Fri Sep 12 16:15:12 2014 - [info] Checking replication filtering settings..
Fri Sep 12 16:15:12 2014 - [info] binlog_do_db= denovo_ng, binlog_ignore_db= information_schema,mysql,test
Fri Sep 12 16:15:12 2014 - [info] Replication filtering check ok.
Fri Sep 12 16:15:12 2014 - [info] Starting SSH connection tests..
Fri Sep 12 16:15:25 2014 - [info] All SSH connection tests passed successfully.
Fri Sep 12 16:15:25 2014 - [info] Checking MHA Node version..
Fri Sep 12 16:15:32 2014 - [info] Version check ok.
Fri Sep 12 16:15:32 2014 - [info] Checking SSH publickey authentication and checking recovery script configurations on the current master..
Fri Sep 12 16:15:38 2014 - [info] Executing command: save_binary_logs --command=test --start_file=mysql-master-bin.000004 --start_pos=4 --binlog_dir=/state/partition1/mysql/data --output_file=/var/tmp/save_binary_logs_test --manager_version=0.52
Fri Sep 12 16:15:38 2014 - [info] Connecting to root@10.1.1.231(10.1.1.231)..
Warning: untrusted X11 forwarding setup failed: xauth key data not generated
Warning: No xauth data; using fake authentication data for X11 forwarding.
Creating /var/tmp if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /state/partition1/mysql/data, up to mysql-master-bin.000004
Fri Sep 12 16:15:45 2014 - [info] Master setting check done.
Fri Sep 12 16:15:45 2014 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Fri Sep 12 16:15:45 2014 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user=root --slave_host=10.1.1.234 --slave_ip=10.1.1.234 --slave_port=63306 --workdir=/var/tmp --target_version=5.6.13-log --manager_version=0.52 --relay_log_info=/state/partition1/mysql/data/relay-log.info --slave_pass=xxx
Fri Sep 12 16:15:45 2014 - [info] Connecting to root@10.1.1.234(10.1.1.234)..
Warning: untrusted X11 forwarding setup failed: xauth key data not generated
Warning: No xauth data; using fake authentication data for X11 forwarding.
Checking slave recovery environment settings..
Opening /state/partition1/mysql/data/relay-log.info ... ok.
Relay log found at /state/partition1/mysql/data, up to compute-0-52-relay-bin.000007
Temporary relay log file is /state/partition1/mysql/data/compute-0-52-relay-bin.000007
Testing mysql connection and privileges..Warning: Using a password on the command line interface can be insecure.
mysql: Unknown OS character set 'ISO-8859-15'.
mysql: Switching to the default character set 'latin1'.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Fri Sep 12 16:15:51 2014 - [info] Slaves settings check done.
Fri Sep 12 16:15:51 2014 - [info]
10.1.1.231 (current master)
+--10.1.1.234
Fri Sep 12 16:15:51 2014 - [info] Checking replication health on 10.1.1.234..
Fri Sep 12 16:15:51 2014 - [info] ok.
Fri Sep 12 16:15:51 2014 - <div class="warning shortcodestyle"></div> master_ip_failover_script is not defined.
Fri Sep 12 16:15:51 2014 - <div class="warning shortcodestyle"></div> shutdown_script is not defined.
Fri Sep 12 16:15:51 2014 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
|