By 叶子与桔子

更新日期:

文章目錄

在项目中为了保证数据不会因为意外丢失，一般都会对数据经行实时的备份，对于mysql数据库来讲通过主从复制机制来热备份。好比我们项目实际使用的db在机器192.169.0.101上，我们要把数据同步到192.169.0.102上进行备份。
首先建立mysql同步账户

101机器上

1
mysql>Grant replication slave on *.* to backup@'192.169.0.102' identified by '123456';

主服务器开启mysql bin-log 日志

101机器上
vim /etc/my.cnf 添加

1
2
3
4
5
6
7
8
[mysqld]
...
# master conf
server-id = 1
log_bin = /data/mysql/log/mysql-bin.log
read-only = 0
#binlog-do-db=game #要同步的数据库，这里注释掉了，表示同步所有数据库
binlog-ignore-db = mysql #忽略的数据库

然后重启mysql服务 service mysqld restart

从服务器多实例设置

这里介绍一台机器上运行多个mysql实例的方式，这样在同一台机器上就可以同时做多个不同mysql数据库的备份了。
102机器上
vim /etc/my.cnf 添加

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin

[mysqld3306]
port = 3306
datadir = /data/mysql/3306/data
socket = /data/mysql/3306/mysql.sock
pid-file = /data/mysql/3306/mysqld.pid
log-error = /data/mysql/3306/mysqld.log
user = mysql
server-id = 3306
read-only = 1
replicate-ignore-db = mysql

[mysqld3307]
port = 3307
datadir = /data/mysql/3307/data
socket = /data/mysql/3307/mysql.sock
pid-file = /data/mysql/3307/mysqld.pid
log-error = /data/mysql/3307/mysqld.log
user = mysql
server-id = 3307
read-only = 1
replicate-ignore-db = mysql

[mysqldump]
quick
max_allowed_packet = 512M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 512M
sort_buffer_size = 32M
read_buffer = 2M 
write_buffer = 2M

[myisamchk]
key_buffer = 512M
sort_buffer_size = 32M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

初始化数据库目录

1
2
3
4
5
6
7
8
9
10
mysql_install_db --datadir=/data/mysql/3306/data --user=mysql
mysql_install_db --datadir=/data/mysql/3307/data --user=mysql
chown -R mysql:root /data
mysqladmin -uroot -S /data/mysql/3306/mysql.sock password 123456 #设置密码
mysql -uroot -S /data/mysql/3306/mysql.sock -p123456 #登录
#或者 mysql -h127.0.0.1 -P3306 -uroot -p123456
select user,host from mysql.user; #查看用户 添加用户
mysql> GRANT ALL PRIVILEGES ON *.* TO 'game'@'localhost' IDENTIFIED BY 'game' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'game'@'%' IDENTIFIED BY 'game' WITH GRANT OPTION;
mysql> flush privileges;

启动mysql实例
mysqld_multi start
关闭mysql实例 关闭需要提供密码
mysqld_multi stop –password=123456
如果不起作用，则用下面语句

1
mysqladmin -h127.0.0.1 -P3307 -uroot -p123456 shutdown

从服务器复制

101机器 查看日志状态
msyql> show master status;
mysql-bin.000001 97

102机器 手动开始同步

1
2
3
4
5
6
mysqladmin -uroot -S /data/mysql/3307/mysql.sock password 123456
mysql -uroot -S /data/mysql/3307/mysql.sock -p123456
msyql>change master to master_host="192.168.0.101",master_user="backup",master_password="123456",master_port=3306,master_log_file="mysql-bin.000001",master_log_pos=0;
     
msyql> start slave;
mysql> SHOW SLAVE STATUS\G

如Slave_IO_Running=Yes Slave_SQL_Running=Yes则成功

注意 从服务器只能同步主服务器bin log里面的内容，如果你想同步的数据库是很久之前创建的，并不在bin log里有记录那就没法同步。
所以在开始同步之前最好先收到把主服务器的所有内容复制到从服务器里，然后在开始同步：

1
2
mysqldump -h192.168.0.101 -P3306 -uroot -p123456 mydb > mydb.sql
mysql -h192.168.0.102 -P3306 -uroot -p123456 mydb < mydb.sql

参考
http://www.cnblogs.com/huangzhen/archive/2012/10/11/2720261.html
http://www.cnblogs.com/lben/archive/2012/11/19/2777632.html
http://www.cnblogs.com/jirglt/p/3549047.html

文章目錄