mysql主从搭建

（以下搭建是主库是新建的无数据，所以从库无需同步主库数据）

1.添加MySQL 用户,创建相应的目录 

useradd -m mysql
mkdir -p /data/mysqltest1 
chown -R mysql:mysql /data/mysqltest 主节点数据文件目录
mkdir -p /data/mysqltest1 
chown -R mysql:mysql /data/mysqltest1 从节点数据文件目录


2.解压二进制软件包
tar -xzvf mysql-5.7.16-linux-glibc2.5-x86_64.tar.gz -C /app/


3.配置mysql.cnf 配置参数文件 
1）mysql.cnf 主节点
basedir='/app/mysql'
datadir='/data/mysqltest'
secure_file_priv=/tmp
log-bin=mysql-bin 
server-id=221
port=3306
socket=/tmp/mysql.sock 


2）mysql.cnf 从节点
basedir='/app/mysql'
datadir='/data/mysqltest1'
secure_file_priv=/tmp
log-bin=mysql-bin 
server-id=222
port=3307
socket=/tmp/mysql1.sock 


4.配置mysql用户环境变量
[mysql@milk ~]$ cat .bash_profile 
# .bash_profile
export PATH=/app/mysql/bin:$PATH
export MYSQL_HOME=/app/mysql
export MYSQL_PS1="\\u@\\h [\\d] \\r:\\m:\\s>>>"


5.初始化并启动主从节点
[mysql@milk ~]$ mysqld --defaults-file=/app/mysql/mysql1.cnf --initialize-insecure
[mysql@milk ~]$ mysqld --defaults-file=/app/mysql/mysql.cnf --initialize-insecure
[mysql@milk ~]$ mysqld --defaults-file=/app/mysql/mysql.cnf &
[mysql@milk ~]$ mysqld --defaults-file=/app/mysql/mysql1.cnf &


6.主节点建立帐户并授权slave（为了主从切换，从节点也建立相关账号）
[mysql@milk ~]$mysql -S /tmp/mysql.sock 

root@localhost [(none)] 12:00:26>>> GRANT REPLICATION SLAVE ON *.* to 'mysqlzc'@'%' identified by 'zc123456';

[mysql@milk ~]$mysql -S /tmp/mysql1.sock 
root@localhost [(none)] 12:01:26>>> GRANT REPLICATION SLAVE ON *.* to 'mysqlzc'@'%' identified by 'zc123456';

7.主节点查询master的状态

[mysql@milk ~]$mysql -S /tmp/mysql.sock 
root@localhost [(none)] 12:00:26>>>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     1033 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+


8.配置从节点slave
[mysql@milk ~]$mysql -S /tmp/mysql1.sock
change master to master_host='192.168.88.8',master_user='mysqlzc',master_port=3306，master_password='zc123456', master_log_file='mysql-bin.000002',master_log_pos=1033;


9.测试主从同步
[mysql@milk ~]$mysql -S /tmp/mysql.sock 
root@localhost [(none)] 12:03:26>>> create database milk;
Query OK, 1 row affected (0.01 sec)
root@localhost [(none)] 12:03:28>>> create table milk.a1 (id int,name char(33));
Query OK, 0 rows affected (0.07 sec)
root@localhost [(none)] 12:03:36>>> insert into milk.a1 values (1,'milk');
Query OK, 1 row affected (0.02 sec)
登录从节点查询
[mysql@milk ~]$mysql -S /tmp/mysql1.sock 
root@localhost [(none)] 12:03:38>>>select * from milk.a1;
+------+------+
| id   | name |
+------+------+
|    1 | milk |
+------+------+

1 row in set (0.00 sec)

10.主从切换
从库执行 stop slave io_thread;
激活从库
执行stop slave; 和 reset master;
将原来主库变为从库
reset slave all;
change master to master_host='192.168.88.8',master_user='mysqlzc',master_port=3307,master_password='zc123456';