Mysql的安装与主从备份

---

1.主数据库配置

vim /etc/my.cnf

my.cnf

[mysql]
server-id = 1
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
log-bin = /data/log_bin
binlog-do-db = test #test为需要备份的数据库
binlog-ignore-db = mysql #mysql为禁止备份的数据库

2.启动主数据库

sudo service iptables stop //关闭防火墙
cd /etc/rc.d/init.d
sudo mysqld start //如果失败请初始化，具体指令：mysql_install_db --user=mysql
mysql -uroot -p //root为账号

#为从数据库新建一个账号test，密码为123456
mysql> grant replication slave on *.* to 'test'@'192.168.108.130' identified by '123456';
show master status\G //获取master里面的File和Position信息
···
File: log_bin.00002
Position: 106
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)

3.从数据库配置

vim /etc/my.inf

my.inf

server-id=2
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
log-bin = /data/log_bin

master-host=192.168.108.129
master-user= test
master-password=123456
master-port=3306
master-connect-retry=60
replicate-do-db=test //同步的数据库
replicate-ignore-db=mysql //被忽略的数据库

4.启动从数据库

cd /etc/rc.d/init.d
sudo mysqld start //如果失败请初始化，具体指令：mysql_install_db --user=mysql
mysql -uroot -p //root为账号

#在mysql的命令行下输入
mysql>change master to
>master_host='192.168.108.129',
>master_user='test',
>master_password='123456',
>master_log_file='log_bin.00002', //log_bin.00002为在主数据库查看的File
>master_log_pos=106; //106为主数据库查看的Position
>slave start; //开启备份

如果两个slave中的Slave_IO_Running、Slave_SQL_Running状态均为Yes则表明设置成功。