本文共 2788 字，大约阅读时间需要 9 分钟。

MySQL 主从实战配置详解（CentOS 7.2）

1. MySQL 配置

1.1 my.cnf 配置

[mysqld]server-id=1log-bin=/usr/local/mysql/binlogbinlog-do-db=msreplicate-do-db=msskip-slave-start=0basedir=/usr/local/mysqldatadir=/usr/local/mysql/datasocket=/tmp/mysql.sockuser=mysqlbind-address=0.0.0.0sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES[mysqld_safe]log-error=/usr/local/mysql/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

[mysqld]server-id=1log-bin=/usr/local/mysql/binlogbinlog-do-db=msreplicate-do-db=msskip-slave-start=0basedir=/usr/local/mysqldatadir=/usr/local/mysql/datasocket=/tmp/mysql.sockuser=mysqlbind-address=0.0.0.0sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES[mysqld_safe]log-error=/usr/local/mysql/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

2. MySQL 登录

mysql -uroot -p12345678

3. MySQL 用户配置

3.1 添加主从账户

grant replication slave on *.* to 'ms'@'10.89.10.91' identified by '12345678';grant replication slave on *.* to 'ms'@'10.89.10.90' identified by '12345678';

3.2 分配主从权限

change master to master_host='10.89.10.91',master_user='ms',master_password='12345678',master_log_file='binlog.91',master_log_pos=154;change master to master_host='10.89.10.90',master_user='ms',master_password='12345678',master_log_file='binlog.90',master_log_pos=154;

4. 启动Slave

start slave;

5. Slave 状态查看

show slave status\G;

6. 数据测试与备份

6.1 创建表

CREATE TABLE T0001 (  F0001 bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',  F0002 varchar(20),  F0003 varchar(30),  PRIMARY KEY (F0001));

6.2 插入数据

INSERT INTO T0001 (F0002, F0003) VALUES ('90R1F2', '90R1F3');INSERT INTO T0001 (F0002, F0003) VALUES ('90R2F2', '90R2F3');INSERT INTO T0001 (F0002, F0003) VALUES ('91R1F2', '91R1F3');INSERT INTO T0001 (F0002, F0003) VALUES ('91R2F2', '91R2F3');

6.3 数据验证

SELECT * FROM T0001;

7. 自动化备份脚本

#!/bin/sh# cpm_backup.sh: 备份 MySQL 数据库并保留最新 5 天备份# MySQL 登录信息db_user="root"db_passwd="12345678"db_host="localhost"# 备份目录backup_dir="/cpmbackup"# 日期格式time=$(date +"%d-%m-%Y")# MySQL 工具路径MYSQL="/usr/local/mysql/bin/mysql"MYSQLDUMP="/usr/local/mysql/bin/mysqldump"MKDIR="/bin/mkdir"RM="/bin/rm"MV="/bin/mv"GZIP="/bin/gzip"# 检查备份目录可写test ! -w $backup_dir && echo "Error: $backup_dir is un-writeable." && exit 0# 创建最新备份目录test ! -d "$backup_dir/backup.0/" && $MKDIR "$backup_dir/backup.0/"# 获取所有数据库列表all_db="$($MYSQL -u $db_user -h $db_host -p$db_passwd -Bse 'show databases')"# 备份所有数据库for db in $all_dbdo  $MYSQLDUMP -u $db_user -h $db_host -p$db_passwd $db | $GZIP -9 > "$backup_dir/backup.0/$time.$db.gz"done# 删除旧备份test -d "$backup_dir/backup.5/" && $RM -rf "$backup_dir/backup.5"# 备份目录轮转for int in 4 3 2 1 0do  if test -d "$backup_dir/backup.$int"  then    next_int=$(expr $int + 1)    $MV "$backup_dir/backup.$int" "$backup_dir/backup.$next_int"  fidone

7.1 CRON 定时任务

在 /etc/crontab 中添加以下行：

01 3 * * * root /cpmbackup/cpm_backup.sh

转载地址：http://cwbfk.baihongyu.com/