一、mysql的主从同步配置
要求,mysql主从数据库,一主两从,其中:
119.161.145.209是主数据库,119.161.145.215是从数据库1,119.161.145.216是从数据库2
mysql服务器IP地址规划
| 主数据库-ERP | 从数据库一(shop1) | 从数据库二(shop2) |
公网IP(eth0) 掩码 网关 私网IP(eth1) | 119.161.145.209 255.255.255.224 119.161.145.193 192.168.10.204 | 119.161.145.215 255.255.255.224 119.161.145.193 192.168.10.206 | 119.161.145.216 255.255.255.224 119.161.145.193 192.168.10.202 |
|
二、Mysql安装信息
my.cnf位置:/data0/mysql/3306/my3306.cnf
定义
socket = /data0/mysql/3306/mysql3306.sock
basedir = /usr/local/webserver/mysql
datadir = /data0/mysql/3306/data
log-error = /data0/mysql/3306/mysql_error.log
include = /usr/local/webserver/mysql/include/mysql
libs = /usr/local/webserver/mysql/lib/mysql
连接本地mysql
mysql -uroot -p123456 -S /data0/mysql/3306/mysql3306.sock
连接远程
mysql -ureplication -prepmysql -h 192.168.10.204
手工启动mysql
/usr/local/webserver/mysql/bin/mysqld_safe --defaults-file=/data0/mysql/3306/my3306.cnf 2>&1 > /dev/null &
手工关闭mysql
/usr/local/webserver/mysql/bin/mysqladmin -u root -p123456 -S /data0/mysql/3306/mysql3306.sock shutdown
脚本启动与关闭mysql
/data0/sh/mysqlservie.sh {start;stop;restart}
以root账号连接到本地ERP数据库
mysql -uroot -p -S /data0/mysql/3306/mysql3306.sock
远程连接到ERP数据库,账号与密码replication:repmysql
mysql -ureplication -prepmysql -h 192.168.10.204
查看ERP服务器 mysql数据库里的用户名
show databases;
use mysql;
三、在ERP上建立商城数据库的账号(主数据库)
1、建立一个replication账号,密码repmysql,只有192.168.10.202可以访问,此账号为数据库同步账号
grant replication slave on *.* to 'replication'@'192.168.10.202' identified by 'repmysql' with grant option;
grant file on *.* to replication@'192.168.10.202' identified by 'repmysql';
grant all privileges on backup.* to replication@'192.168.10.202' identified by 'repmysql';
同理建立206与210的replication账号
grant replication slave on *.* to 'replication'@'192.168.10.206' identified by 'repmysql' with grant option;
grant file on *.* to replication@'192.168.10.206' identified by 'repmysql';
grant all privileges on backup.* to replication@'192.168.10.206' identified by 'repmysql';
grant replication slave on *.* to 'replication'@'192.168.10.210' identified by 'repmysql' with grant option;
grant file on *.* to replication@'192.168.10.210' identified by 'repmysql';
grant all privileges on backup.* to replication@'192.168.10.210' identified by 'repmysql';
flush privileges;
2、顺便建立网站的读写分离账号:oucampdb_write与oucampdb_read
A:建立oucampdb_write账号,密码123456,192.168.10网段都可以登录。此账号为商城读写分离数据库的主库写账号
INSERT INTO user (Host,User,Password,Select_priv,Insert_priv,Update_priv,Delete_priv) VALUES ('192.168.10.%','oucampdb_write',PASSWORD('123456'),'Y','Y','Y','Y');
B:建立oucampdb_read账号,密码123456,192.168.10网段都可以登录。此账号为商城读写分离数据库的主库读账号
INSERT INTO user (Host,User,Password,Select_priv,Insert_priv,Update_priv,Delete_priv) VALUES ('192.168.10.%','oucampdb_read',PASSWORD('123456'),'Y','Y','Y','Y');
flush privileges;
刷新
flush privileges;
select * from mysql.user;
root | localhost |
replication | 192.168.10.202 |
oucampdb_read | % 127.0.0.1 |
oucampdb_ write | 192.168.10.% |
nagios | 192.168.10.210 |
命令备注
修数据库账号,改用户名oucampdb_write为oucampdb_read
update user set User='oucampdb_read' where Host='192.168.10.%';
四、修改主与从数据库的my.cnf
在ERP的mysql的配置文件/data0/mysql/3306/my3306.cnf中的[mysqld]中增加
log-bin
server-id
在shop1的mysql的配置文件/data0/mysql/3306/my3306.cnf中的[mysqld]中增加
log-bin
server-id
binlog-do-db
binlog-ignore-db
master-host
master-user
master-password
master-port
replicate-do-db
replicate-ignore-db
master-connect-retry = 10
slave-skip-errors
在shop2的mysql的配置文件/data0/mysql/3306/my3306.cnf中的[mysqld]中增加
log-bin
server-id
binlog-do-db
binlog-ignore-db
master-host
master-user
master-password
master-port
replicate-do-db
replicate-ignore-db
master-connect-retry = 10
slave-skip-errors
五、mysql的同步配置
1、主库锁库,获取MASTER_LOG_FILE与MASTER_LOG_POS的值,并且导出数据库的备份
a:主库锁库
flush tables with read lock;
b:使用命令show master status查看主库的MASTER_LOG_FILE与MASTER_LOG_POS的值,手工做从库同步主库在调试,需要这2个参数:
show master status;
c:解锁(先不要执行这个操作,2个从库同步之后再解锁)
unlock tables;
2、在主库上导出要备份的数据库并SCP到从库1和2
cd /data0/mysql/3306/
/usr/local/webserver/mysql/bin/mysqldump -u root -p -S /data0/mysql/3306/mysql3306.sock oucamp_db > oucamp_db.sql
scp oucamp_db.sql root@192.168.10.206:/data0/mysql/3306/
scp oucamp_db.sql root@192.168.10.202:/data0/mysql/3306/
3、在从库上导入备份的oucamp_db.sql,分别在shop1与shop2两个从库上操作
cd /data0/mysql/3306/
mysql -uroot -p -S /data0/mysql/3306/mysql3306.sock
输入数据库root密码
删除oucamp_db数据库
drop database oucamp_db
建立新的oucamp_db数据库
create database oucamp_db;
导入数据库sql
use oucamp_db;
source oucamp_db.sql;
配置mysql同步,从库(slave database)修改完my.cnf后,需要执行的语句,注意MASTER_LOG_FILE与MASTER_LOG_POS的值要在主库获取(见上)
slave stop;
CHANGE MASTER TO MASTER_HOST='192.168.10.204',
MASTER_PORT=3306,
MASTER_USER='replication',
MASTER_PASSWORD='repmysql',
MASTER_LOG_FILE='binlog.000054',
MASTER_LOG_POS=2697328;
slave start;
注:执行从库的CHANGE MASTER命令,主库必须在锁表状态,否则同步出1032错误
查看从库状态
show slave status\G;
quit
六、查看mysql的错误日志,检测同步状态是否有错误
tail -30 /data0/mysql/3306/mysql_error.log
没有错误的话,主库可以执行unlock tables解锁
测试主从数据库同步,在主库上建立一个test表,插入一条数据,然后在从库上看这个表以及这条数据是否存在:
主库上操作
mysql -uroot -p -S /data0/mysql/3306/mysql3306.sock
use oucamp_db;
create table test(id int,name varchar(20),company varchar(40));
insert into test values(1,'aaa','wwwwwwwww');
select * from oucamp_db.test;
从库上操作
mysql -uroot -p -S /data0/mysql/3306/mysql3306.sock
use oucamp_db;
select * from oucamp_db.test;
查询mysql版本号
SHOW VARIABLES LIKE 'version';
查询mysql服务状态
ps -ef |grep mysql