MySQL数据库主从同步(单台2实例)

一 前戏
#安装
yum install -y mysql-server mysql mysql-devel
#不同实例目录
mkdir /data/{3306,3307} -p
# 授权
chown -R mysql.mysql /data

二 配置文件与启动

#删除或者重命名原/etc/my.cnf配置文件
mv /etc/my.cnf /etc/my.cnf.bak
#创建配置文件 vi /data/3306/my.cnf （以下为配置文件内容）
# 配置文件从这里开始
[client]
port = 3306
socket = /data/3306/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /usr #编译和二进制安装按实际目录填写路径（/usr为yum安装的）
datadir = /data/3306/data
open_files_limit = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_cache = 614
external-locking = FALSE
max_allowed_packet =8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
thread_stack = 192K
tmp_table_size = 2M
max_heap_table_size = 2M
long_query_time = 1
pid-file = /data/3306/mysql.pid
log-bin = /data/3306/mysql-bin #主从同步的关键点，从库上不需要开启
relay-log = /data/3306/relay-bin
relay-log-info-file = /data/3306/relay-log.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
expire_logs_days = 7
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db=mysql
server-id = 1 #主库从库ID 不可相同
[mysqldump]
quick
max_allowed_packet = 2M
[mysqld_safe]
log-error=/data/3306/mysql3306.err
pid-file=/data/3306/mysqld.pid
# 配置文件到这里结束

=========================================

3 创建启动脚本 vi /data/3306/mysql
#启动脚本从这里开始
#!/bin/sh
port=3306
user="root"
pwd="123456" #按实际密码填写
Path="/usr/bin" #按实际安装指定mysql可执行程序的路径（可用which mysql查找）
sock="/data/${port}/mysql.sock"
start_mysql()
{
if [ ! -e "$sock" ];then
printf "Starting MySQL...\n"
/bin/sh ${Path}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
else
printf "MySQL is running...\n"
exit
fi
}
stop_mysql()
{
if [ ! -e "$sock" ];then
printf "MySQL is stopped...\n"
exit
else
printf "Stoping MySQL...\n"
${Path}/mysqladmin -u ${user} -p${pwd} -S /data/${port}/mysql.sock shutdown
fi
}
restart_mysql()
{
printf "Restarting MySQL...\n"
stop_mysql
sleep 2
start_mysql
}
case $1 in
start)
start_mysql
;;
stop)
stop_mysql
;;
restart)
restart_mysql
;;
*)
printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac
#启动脚本到此结束

chmod +x /data/3306/mysql #授权可执行

===============================================================

4 为3307也创建一样的一份，注意把所有3306替换3307 修改不同的server-id
cp /data/3306/mysql /data/3307/
cp /data/3306/my.cnf /data/3307/
sed -i 's/server-id = 1/server-id = 2/g' /data/3307/my.cnf
sed -i 's/3306/3307/g' /data/3307/my.cnf
sed -i 's/3306/3307/g' /data/3307/mysql

三 初始化2个实例
#初始化3306端口数据库
mysql_install_db --datadir=/data/3306/data --user=mysql
#启动3306数据库
/data/3306/mysql start
#修改3306实例登录密码
mysqladmin -uroot password '123456' -S /data/3306/mysql.sock
mysql_install_db --datadir=/data/3307/data --user=mysql
/data/3307/mysql start
mysqladmin -uroot password '123456' -S /data/3307/mysql.sock

四 配置主库
#登录3306实例（主库）
mysql -uroot -p123456 -S /data/3306/mysql.sock
#授权同步的帐号密码
grant replication slave on *.* to rep@'%' identified by'66888888';
#刷新权限
flush privileges;
#退出
exit
#主库锁表
mysql -uroot -p123456 -S /data/3306/mysql.sock -e "flush tables with read lock;"
#查看主库binlog状态
mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show master status;" >/mysql.log
#备份主库
mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B |gzip >/mysql.sql.gz
#主库解锁
mysql -uroot -p123456 -S /data/3306/mysql.sock -e "unlock tables;"
备注：执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

五 配置从库
#导入主库的备份
gzip -d /mysql.sql.gz
mysql -uroot -p'123456' -S /data/3307/mysql.sock < /mysql.sql
mysql -uroot -p'123456' -S /data/3307/mysql.sock
#设置同步信息
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.1.7', #服务器IP
-> MASTER_PORT=3306, #主库端口
-> MASTER_USER='rep', #同步的用户
-> MASTER_PASSWORD='66888888', #同步的用户密码
-> MASTER_LOG_FILE='mysql-bin.000002', #binlog文件（这个信息在/mysql.log里哦）
-> MASTER_LOG_POS=424; #位置点（这个信息在/mysql.log里哦）

备注：可以一次设置以上信息change master to master_host='192.168.145.222',master_user='mysync',master_password='q123456',
master_log_file='mysql-bin.000004',master_log_pos=308;
#开启从模式
mysql> start slave;
#查看状态
mysql -uroot -p'123456' -S /data/3307/mysql.sock -e "show slave status\G"|egrep "Seconds_Behind_Master|_Running"
#2个Yes表示成功，如不成功最多可能是上一步帐号同步设置可能有问题。