Centos下Mysql多主机master单机多实例slave同步配置
环境
操作系统:
1、centos6.3
2、mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
IP:
1、master1:192.168.17.130
2、Master2:192.168.17.131
3、Slave:192.168.17.132
Mysql安装
因为在centos下 ,使用yum install -y mysql-server mysql-devel 直接安装。
Master1 master2配置
1、创建测试
#service mysqld start
#mysql
mysql>create database www;
mysql>use www;
mysql>create table www(id int);
mysql>insert into www values(1);
mysql> select * from www;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.01 sec)
mysql>create database blog;
mysql>use blog;
mysql>create table blog(id int);
mysql>insert into blog values(1);
mysql> select * from blog;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
2、配置my.cnf
vim /etc/my.cnf
注意一下2个参数,这一段在[mysqld]节点中添加
log-bin=mysql-bin //日志为2进制,不需要更改
server-id =1 //为1就是Master,不需要更改
binlog-do-db=blog //要同步的库
binlog-do-db=www //要同步的库
binlog-ignore-db=mysql,test,information_schema //是不要记录日志的数据库名,多个数据库中间用逗号(,)隔开
然后把innodb前面的#去掉,结果如下
innodb_data_home_dir = /var/lib/mysql/ //innodb的表空间位置
innodb_data_file_path = ibdata1:50M:autoextend //表空间的名字,开始50M
innodb_log_group_home_dir = /var/lib/mysql/
innodb_buffer_pool_size = 256M //为系统内存的50-80%
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
然后启动数据库,让配置文件生效
#service mysqld restart
3、配置slave同步的账号
#mysql -u root -p
mysql>grant replication slave on *.* to repluser@'192.168.17.132 identified by '123456';
mysql>flush privileges;
格式:GRANT REPLICATION SLAVE ON *.* TO '帐号'@'从服务器IP或主机名' IDENTIFIED BY '密码';
4、备份master数据库数据
mysql> flush tables with read lock; //不要退出这个终端,否则这个锁就不生效了。从服务器的数据库建好后。在主服务器执行解锁
同时要记录下mysql-bin.000003和1271
mysql> show master status;
+------------------+----------+--------------+-------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+-------------------------------+
| mysql-bin.000002 | 106 | blog,www | mysql,test,information_schema |
+------------------+----------+--------------+-------------------------------+
1 row in set (0.01 sec)
取得快照并记录日志名和偏移量
开启另一个终端对主服务器数据目录做快照。
#cd /usr/local/mysql/data
#tar -zcvf backup.tar.gz www blog
此时在主库解开table的锁定
mysql> unlock tables;
Slave多实例配置
1、数据同步
把从主数据库服务器备份出来的数据库导入到从服务器中
先用scp把主服务器上的backup.tar.gz拷贝过来,解压到/usr/local/mysql/data目录
#cd /usr/local/mysq/data
#tar -zxvf backup.tar.gz
2、配置my.cnf:mysqld_multi
1.修改my.cnf
如一个定义两个实例的参考配置:
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = root
password = 123456
[mysqld1]
port = 3306
socket = /tmp/mysql3306.sock
datadir=/data/db/my1
user=root
server-id = 2
master-host = 192.168.17.130
master-user = repluser
master-password = 123456
master-port = 3306
master-connect-retry=60
replicate-do-db=www
replicate-do-db=blog
#log-slave-updates
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld2]
port = 3307
socket = /tmp/mysql3307.sock
datadir=/data/db/my2
user=root
server-id = 3
master-host = 192.168.17.131
master-user = repluser
master-password = 123456
master-port = 3306
master-connect-retry=60
replicate-do-db=www
replicate-do-db=blog
#log-slave-updates
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
3、创建实例存储位置
mkdir -p /data/db/my1
mkdir -p /data/db/my2
chown -R mysql:mysql /data
4、初始化DB
/usr/local/mysql/scripts/mysql_install_db --datadir=/data/db/my1/ -umysql
/usr/local/mysql/scripts/mysql_install_db --datadir=/data/db/my2/ -umysql
5、管理多实例启停
1.mysql启动
mysqld_multi start 1 启动实例1
mysqld_multi start 1-2 启动实例1,2
2.mysql关闭
mysqld_multi stop 1 关闭实例1
mysqld_multi stop 1-2 关闭实例1,2
想要停止 配置的账号必须得修改密码:
密码修改方法一:
mysql>user mysql;
>update user set password=PASSWORD('新密码') where user='root';
密码修改方法二:
mysqladmin -uroot -P3306 password '新密码' -S /usr/local/mysql/data/3306/mysql.sock
3.命令行登陆实例2
mysql -u your_user -p your_password -P3307 -S /tmp/mysql3307.sock
Slave同步配置
1、配置my.cnf
vim /etc/my.cnf 增加以下几项
server-id = 2
master-host = 10.80.11.203
master-user = repluser
master-password = 123456
master-port = 3306
master-connect-retry=60
replicate-do-db=www //告诉slave只做www数据库的更新
replicate-do-db=blog //告诉slave只做blog数据库的更新
2、设置同步参数
启动从库服务器
#service mysqld start
停止slave服务,设置主服务器的各种参数
#mysql
mysql>slave stop;
然后敲入以下代码,一行一行的复制
change master to
MASTER_HOST='192.168.17.130',MASTER_USER='repluser',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=106;
mysql> slave start;
然后在master上插入记录,会发现在slave上也会出现
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.80.11.203
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1271
Relay_Log_File: squid2-relay-bin.000004
Relay_Log_Pos: 617
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: www,blog
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: 1271
Relay_Log_Space: 773
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:
1 row in set (0.01 sec)
从节点2的配置跟从节点1的配置一样,只是server-id 改成3
最重要是以下三点:
Slave_IO_Running:
是否要从 Master Server 复製 Binary Log 资料,必须为 Yes。
Slave_SQL_Running:
是否要执行从 Master Server 复製过来的 Binary Log 资料,必须为 Yes。
Seconds_Behind_Master:
Slave 的资料落后了 Master 多少秒,执行一段时间后应该会是零。
然后在master上更新数据,会发现在2台slave上都能查询到数据,本实验测试成功。
错误解决办法
查看日志:
/var/log/mysqld.log
/usr/share/mysqld_multi.log
报错日志+perrot(errorid)
Slave_IO_Running:NO
1、网络
2、账号密码
3、权限问题
Slave_SQL_Running: NO
原因:
1.程序可能在slave上进行了写操作
2.也可能是slave机器重起后,事务回滚造成的.
解决办法I:
1.首先停掉Slave服务:slave stop
2.到主服务器上查看主机状态:
记录File和Position对应的值。
mysql> show master status;
+------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000020 | 135617781 | | |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)
3.到slave服务器上执行手动同步:
mysql> change master to
> master_host='master_ip',
> master_user='user',
> master_password='pwd',
> master_port=3307,
> master_log_file='mysql-bin.000020',
> master_log_pos=135617781;
1 row in set (0.00 sec)
mysql> slave start;
1 row in set (0.00 sec)
再次查看slave状态发现:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Seconds_Behind_Master: 0
解决办法II:
mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;
自己的使用体会:方法一是强制性从某一个点开始同步,会有部分没有同步的数据丢失,后续主服务器上删除记录同步也会有一些错误信息,不会影响使用.方法二不一定会有效果.
=======================================================================================]
1,主从不能同步:
show slave status;报错:Error xxx dosn't exist
且show slave status\G:
Slave_SQL_Running: NO
Seconds_Behind_Master: NULL
解决方法:
stop slave;
set global sql_slave_skip_counter =1 ;
start slave;
之后Slave会和Master去同步 主要看:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master是否为0,0就是已经同步了
2,还需要做的一些优化与监视:
show full processlist; //查看mysql当前同步线程号
skip-name-resolve //跳过dns名称查询,有助于加快连接及同步的速度
max_connections=1000 //增大Mysql的连接数目,(默认100)
max_connect_errors=100 //增大Mysql的错误连接数目,(默认10)
查看日志一些命令
1, show master status\G;
在这里主要是看log-bin的文件是否相同。
show slave status\G;
在这里主要是看:
Slave_IO_Running=Yes
Slave_SQL_Running=Yes
如果都是Yes,则说明配置成功.
2,在master上输入show processlist\G;
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 2
User: root
Host: localhost:32931
db: NULL
Command: Binlog Dump
Time: 94
State: Has sent all binlog to slave; waiting for binlog to
be updated
Info: NULL
如果出现Command: Binlog Dump,则说明配置成功.
stop slave #停止同步
start slave #开始同步,从日志终止的位置开始更新。
SET SQL_LOG_BIN=0|1 #主机端运行,需要super权限,用来开停日志,随意开停,会造成主机从机数据不一致,造成错误
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n # 客户端运行,用来跳过几个事件,只有当同步进程出现错误而停止的时候才可以执行。
RESET MASTER #主机端运行,清除所有的日志,这条命令就是原来的FLUSH MASTER
RESET SLAVE #从机运行,清除日志同步位置标志,并重新生成master.info
虽然重新生成了master.info,但是并不起用,最好,将从机的mysql进程重启一下,
LOAD TABLE tblname FROM MASTER #从机运行,从主机端重读指定的表的数据,每次只能读取一个,受timeout时间限制,需要调整timeout时间。执行这个命令需要同步账号有 reload和super权限。以及对相应的库有select权限。如果表比较大,要增加net_read_timeout 和 net_write_timeout的值
LOAD DATA FROM MASTER #从机执行,从主机端重新读入所有的数据。执行这个命令需要同步账号有reload和super权限。以及对相应的库有select权限。如果表比较大,要增加net_read_timeout 和 net_write_timeout的值
CHANGE MASTER TO master_def_list #在线改变一些主机设置,多个用逗号间隔,比如
CHANGE MASTER TO
MASTER_HOST='master2.mycompany.com',
MASTER_USER='replication',
MASTER_PASSWORD='bigs3cret'
MASTER_POS_WAIT() #从机运行
SHOW MASTER STATUS #主机运行,看日志导出信息
SHOW SLAVE HOSTS #主机运行,看连入的从机的情况。
SHOW SLAVE STATUS (slave)
SHOW MASTER LOGS (master)
SHOW BINLOG EVENTS [ IN 'logname' ] [ FROM pos ] [ LIMIT [offset,] rows ]
PURGE [MASTER] LOGS TO 'logname' ; PURGE [MASTER] LOGS BEFORE 'date'