mysql主从备份的过程_mysql主从同步-多实例主从备份-简要步骤

1,[M][S]改配置s:my.cnf

log-bin = mysql-bin  # 日志文件名

server-id = 1 # 主数据库端ID号

注意:slave开不开都行,log_bin可开可不开,server-id不一样就ok.

如果做级联的话,要开.且要额外开log-slave-update

2,[M]建用户s:grant replication slave on *.* to 'rep'@'192.168.100.%' identified by '123456';

flush privileges;

2,[M]锁主表flush table with read lock;

3,[M]备份mysqldump -uroot -S /data/3306/mysql.sock -A -B --events|gzip > /opt/req.sql.gz

mysqldump -uroot -S /data/3306/mysql.sock -A -B --events --master-data=1 > /opt/req.sql

注:备份完事后解锁

unlock tables;

4,[S]导入数据到从库mysql -uroot -S /data/3307/mysql.sock 

5,[S]

CHANGE MASTER TO

MASTER_HOST='master_host_name',

MASTER_USER='replication_user_name',

MASTER_PASSWORD='replication_password',

MASTER_LOG_FILE='recorded_log_file_name',

MASTER_LOG_POS=recorded_log_position;

CHANGE MASTER TO

MASTER_HOST='192.168.14.138',

MASTER_PORT=3306,

MASTER_USER='rep',

MASTER_PASSWORD='123456',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=335;

6,[S]start slave;