MYSQL 双机主从安装配置
主库IP：10.4.58.151
从库IP：10.4.58.152
配置前：
1. 主从服务器分别安装同版本的mysql数据库
2. 在主从服务器分别创建日志文件夹/home/mysql/log，并授权给mysql用户
配置：
1. 创建同步用户：
在从库创建同步用户：create user replication identified by '123456';
在主库授权用户：
grant replication slave on *.* to 'replication'@'10.4.58.152' identified by '123456';
Flush privileges;
2. 创建测试数据库：
在主库创建数据库：
create database test1;
use test1;
create table user(id int(4), name varchar(20));
insert into user values (1,'abc');
insert into user values (2,'abc');
insert into user values (3,'abc');
3. 修改主库配置文件：/etc/my.inf
添加在[mysqld]下面
log-bin=mysql-bin
server-id=1
log-bin=/home/mysql/log/updatelog
binlog-do-db=test1
binlog-ignore-db=mysql,test
4. 导出主库中的测试库：
mysqldump -uroot -p123456 --database test1 > /tmp/test1.sql
5. 传输到从库服务器上：
scp /tmp/test1.sql root@10.4.58.152:/tmp
6.重启主库
/etc/init.d/mysqld restart
7. 修改从库配置文件：/etc/my.inf
添加在[mysqld]下面
server-id=2
master_host=10.4.58.152
master_user=replication
master_password=123456
master_port=3306
master_connect-retry=60
relicate-ignore-db=mysql
replicate-do-db=test1
8. 重启从库
/etc/init.d/mysqld restart
9. 从库导入测试库
mysql -uroot -p
source /tmp/test1.sql
10. 启动从库进程：
start slave
如果启动失败：
stop slave
change master to ....
11.查看同步状态：
主库：show master status;
从库：show slave status\G;
12.生产库运行my.cnf文件
主库：
[mysqld]
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
basedir=/home/mysql
datadir=/home/mysql/data
log-bin=mysql-bin
server-id=1
log-bin=/home/mysql/log/updatelog
binlog-ignore-db=mysql,test
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
从库：
[mysqld]
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
datadir=/home/mysql/data
basedir=/home/mysql
server-id=2
log_bin=/home/mysql/log/updatelog
master_host=10.4.58.151
master_user=replication
master_password=123456
master_port=3306
master_connect-retry=60
replicate-ignore-db=mysql
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Comments
Post a Comment