mysql master-slave备份配置文档

安装mysql

yum -y install mysql-server

yum -y install php-mysql

配置mysql

Heartbeat1：

service mysqld start

mysql -u root -p

GRANT REPLICATION SLAVE,REPLICATION CLIENT,RELOAD,SUPER ON*.* TO ‘cnic’@’heartbeat2′ IDENTIFIED BY ‘cnic’;

flush privileges;

exit;

service mysqld stop

mkdir /var/log/mysql

chown mysql.mysql /var/log/mysql/

vi /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommendedto prevent assorted security risks

symbolic-links=0

default-character-set = utf8

server-id = 1

binlog-do-db = test

log-bin=/var/log/mysql/updatelog

binlog-ignore-db = mysql

 

[mysql]

default-character-set = utf8

 

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

 

service mysqld start

Heartbeat2:

service mysqld start

mysql -u root -p

GRANT REPLICATION SLAVE,REPLICATION CLIENT,RELOAD,SUPER ON*.* TO ‘cnic’@’heartbeat1′ IDENTIFIED BY ‘cnic’;

flush privileges;

exit;

service mysqld stop

mkdir /var/log/mysql

chown mysql.mysql /var/log/mysql/

 

vi /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommendedto prevent assorted security risks

symbolic-links=0

default-character-set = utf8

log-bin=/var/log/mysql/updatelog

server-id = 2

master-host = 192.168.190.108

master-user = cnic

master-password = cnic

master-port = 3306

master-connect-retry = 60

replicate-ignore-db = mysql

replicate-do-db = test

 

[mysql]

default-character-set = utf8

 

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

 

service mysqld start

测试mysql

Show Slave status:此处Slave_IO_Running ,Slave_SQL_Running 都应该是yes,表示从库的I/O,Slave_SQL线程都正确开启。

在Mysql中可通过以下命令来查看主从状态

show master status 查看master状态

show slave status 查看slave状态

show processlist G 查看当前进程

stop slave 暂时停止slave进程

start slave 开始slave进程

 

总结

如果master挂掉了，数据只会录入slave，等你把master重启起来，那数据就同步不回master。