Mysql数据库单向同步（一主两从）

来源：互联网发布：编程猫软件下载编辑：程序博客网时间：2024/06/07 22:26

一、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数据库单向同步（一主两从）文档

二、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
127.0.0.1

replication

192.168.10.202
192.168.10.206
192.168.10.210

oucampdb_read

127.0.0.1
192.168.10.202
192.168.10.206
192.168.10.210
192.168.10.%

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 = /data0/mysql/3306/binlog

server-id = 3

在shop1的mysql的配置文件/data0/mysql/3306/my3306.cnf中的[mysqld]中增加

log-bin = /data0/mysql/3306/binlog

server-id = 4

binlog-do-db = oucamp_db

binlog-ignore-db = mysql

master-host = 192.168.10.204

master-user = replication

master-password = repmysql

master-port = 3306

replicate-do-db = oucamp_db

replicate-ignore-db = mysql

master-connect-retry = 10

slave-skip-errors = 1032

在shop2的mysql的配置文件/data0/mysql/3306/my3306.cnf中的[mysqld]中增加

log-bin = /data0/mysql/3306/binlog

server-id = 6

binlog-do-db = oucamp_db

binlog-ignore-db = mysql

master-host = 192.168.10.204

master-user = replication

master-password = repmysql

master-port = 3306

replicate-do-db = oucamp_db

replicate-ignore-db = mysql

master-connect-retry = 10

slave-skip-errors = 1032

五、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