MySql主从复制搭建方法
1、先安装mysql的yum源
rpm -ivh mysql-community-release-el7-5.noarch.rpm
2、再安装mysql server端
yum -y install mysql-community-server
3、启动mysql
systemctl enable mysqld
systemctl start mysqld
4、安装完成之后需要进行初始化，我这里直接写了一个shell的函数进行初始化，可以复制后直接调用。也可以手动初始化
Initialization_Mysql()
{
if [ ! -f /usr/bin/expect ]; then
yum -y install expect
fi
expect<<- END
spawn mysql_secure_installation
expect "Enter current password for root (enter for none):"
send "\n"
expect "Set root password?"
send "y\n"
expect "New password:"
send "123456\n"
expect "Re-enter new password:"
send "123456\n"
expect "Remove anonymous users?"
send "y\n"
expect "Disallow root login remotely?"
send "y\n"
expect "Remove test database and access to it?"
send "y\n"
expect "Reload privilege tables now?"
send "y\n"
expect eof
exit
END
}
5、编辑主库、从库上的配置文件/etc/my.cnf
主库在[mysqld]下增加
server-id = 1
log-bin= binlog
binlog_format=mixed
从库在[mysqld]下增加
server-id=2
log-bin= binlog
binlog_format=mixed
replicate_wild_do_table=oldboy.%
replicate_wild_ignore_table=mysql.%
6、重启主库、从库mysql服务使配置生效
systemctl restart mysqld
7、在主库上创建slave用户，并指定该用户只能在主机192.168.137.128上登录
mysql>grant replication slave on *.* to 'admin'@'192.168.137.128' identified by '123456';
8、在从库192.168.137.128上登录到主库192.168.137.139
mysql -u admin -h 192.168.137.139 -p
9、在主库上查状态
select user,host from mysql.user;
10、锁主库的表并查询master的状态值，供步骤14使用
flush tables with read lock;
show master status;
11、在主库上备份adserver
mysqldump --master-data -u root -p oldboy> oldboy.sql
并将 oldboy.sql 通过scp拷贝到从库上
12、在从库上创建oldboy
mysql> create database oldboy;
Query OK, 1 row affected (0.00 sec)
[root@master ~]# mysql -u root -p oldboy< /root/oldboy.sql
Enter password:
[root@master ~]#
13、解锁主库表unlock tables;
14、在从库上执行
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.137.139',master_user='admin',master_password='123456',master_log_file='binlog.000001', master_log_pos=333;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
15、从库上查看slave status 状态
mysql>show slave status\G;
16、mysql主从同步搭建完成
主库insert，从库可以select