mysql 主从部署

在ubuntu 16上，配置mysql 主从服务器

查看mysql主从命令
show variables like '%server_id%';
show variables like 'log_bin'; 
show master status;
show slave status;
show processlist;

卸载mysql
sudo apt remove mysql-common
sudo apt autoremove --purge mysql-server-5.7

安装mysql
sudo apt-get install mysql-server

1  修改master配置文件

1) 执行vi /etc/mysql/my.cnf命令修改配置文件，添加以下内容

[mysqld]
server-id = 1
replicate-do-db= test
binlog-format="mixed"
log_bin  = /var/log/mysql/mysql-bin.log

2) 启动远程访问

cd /etc/mysql/mysql.conf.d

vi mysqld.cnf 
bind-address        = 0.0.0.0    原来是127.0.0.1
 

3)  重启

[root@test ~]# service mysql restart

4)  创建用户并授权

# 创建用户
mysql> grant replication slave on *.* to 'test'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

# 用户授权
mysql> grant all on *.* to 'test'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

# 刷新权限
mysql> Flush privileges;
Query OK, 0 rows affected (0.01 sec)

5) 查看二进制文件名以及大小

show master status;

2 修改slave配置文件

1) vi /etc/mysql/my.cnf
[mysqld]
server-id = 2
binlog-format="mixed"
log_bin  = /var/log/mysql/mysql-bin.log

重启mysql服务
[root@localhost ~]# service mysql restart

2)  配置主数据库信息

mysql> change master to master_host='192.168.1.5',master_user='aaa',master_password='12345678',master_log_file='mysql-bin.000001',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

3)  开始同步

mysql> start slave;
mysql> stop slave;

4) 远程连接

mysql -h192.168.5.11 -P3306 -uroot -p12345678