mysql数据库主从同步配置

1,建立时间同步环境,在主节点搭建时间同步服务器

#yum install -y ntp

# vim /etc/ntp.conf 在配置文件添加两行:

server 127.127.1.0

fudge  127.127.1.0  stratum 8

在主节点上启动ntp服务

# service ntpd start

在从节点上同步时间

# ntpdate  192.168.1.109

在双节点上关闭iptables和selinux

# service iptables stop

# setenforce  0

2,配置主mysql节点

在/etc/my.cnf中修改或者增加下面内容

server-id=12809

log-bin=master-bin

log-slave-updates=true

#注意:server-id不能与主服务器相同

重启mysql之后,授权用户

# /etc/init.d/mysqld restart

mysql> grant  replication slave on *.* to 'myslave'@'192.168.1.%' identified by '123.pwd';

mysql> flush privileges;

mysql> show  master status;

+-------------------+----------+--------------+------------------+-------------------+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-------------------+----------+--------------+------------------+-------------------+

| master-bin.000001 |      410 |              |                  |                   |

+-------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

#其中File列显示日志名,Position列显示偏移量,这两个值在后面配置从服务器的时候需要。Slave应从该点在Master上进行新的更新

3,配置从mysql节点

3.1)在/etc/my.cnf中修改或者增加下面内容

server-id=12889

relay-log=relay-log-bin

relay-log-index=slave-relay-bin.index

3.2)重启mysql之后,登录mysql并按主服务器结果更改下面命令中master_log_file和master_log_pos 参数

# service mysqld restart

[以下命令的单引号是键盘第二排第一个按键.]

mysql> change master to

-> master_host='192.168.1.109',

-> master_user='myslave',

-> master_password='123.pwd',

->  master_log_file='master-bin.000001',

-> master_log_pos=410;

Query OK, 0 rows affected, 2 warnings (0.07 sec)

3.3)启动同步从主服务器同步数据

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

3.4)查看slave状态,确保以下两个值为yes

mysql> show  slave status \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.109

Master_User: myslave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000001

Read_Master_Log_Pos: 410

Relay_Log_File: relay-log-bin.000002

Relay_Log_Pos: 284

Relay_Master_Log_File: master-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

4,验证主从复制效果

4.1)在主节点登录mysql

mysql> create database bbs;

Query OK, 1 row affected (0.05 sec)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| bbs                |

| mysql              |

| performance_schema |

| test               |

+--------------------+

5 rows in set (0.00 sec)

4.2)查看从节点的数据库,发现有新建的数据库。同步成功!

[root@mysql ~]# mysql -u root -p123.pwd -h192.168.1.167 -e 'show databases;'

Warning: Using a password on the command line interface can be insecure.

+--------------------+

| Database           |

+--------------------+

| information_schema |

| bbs                |

| mysql              |

| performance_schema |

| test               |

+--------------------+

#从数据库#的操作:

reset slave; 重置从数据库的同步

stop slave; 停止同步

start slave; 开始同步

show slave status \G; 查看从数据库的状态

#主数据库#的操作:

show master status; 查看主数据库的主机状态

mysql数据库主从同步配置


发表评论

登录 后发表评论.