mysql主从
主机清单：
1、基础配置
1、关闭防火墙
#两台都关 [root@localhost ~]# systemctl stop firewalld [root@localhost ~]# systemctl disable firewalld
2、seliunx 关闭
root@localhost ~]# setenforce 0 [root@localhost ~]# vi /etc/selinux/config # This file controls the state of SELinux on the system. # SELINUX= can take one of these three values: # enforcing - SELinux security policy is enforced. # permissive - SELinux prints warnings instead of enforcing. # disabled - No SELinux policy is loaded. SELINUX=disabled # SELINUXTYPE= can take one of three values: # targeted - Targeted processes are protected, # minimum - Modification of targeted policy. Only selected processes are protected. # mls - Multi Level Security protection. SELINUXTYPE=targeted
3、配置yum源
[root@localhost ~]# cd /etc/yum.repos.d/ [root@localhost yum.repos.d]# rm -rf * [root@localhost yum.repos.d]# curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo [root@localhost yum.repos.d]# yum clean all [root@localhost yum.repos.d]# yum makecache
2、配置:
1、主服务器（master）
1、安装mysql
#1、安装mysql： [root@localhost ~]# yum -y install mariadb-server #2、重启mysql： [root@localhost ~]# systemctl restart mariadb #3、修改MySQL密码（000000） [root@localhost ~]# mysql_secure_installation
2、配置mysql文件
编辑主服务器的 MySQL 配置文件（一般是 my.cnf 或 my.ini），启用二进制日志（binary logging） 功能。 [root@localhost ~]# vim /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd ###添加 log-bin=mysql-bin server-id=1 #这将启用二进制日志，并为主服务器指定一个唯一的 server-id。 #重启从服务器的 MySQL 服务，使配置生效 [root@localhost ~]# systemctl restart mariadb
3、创建复制用户，并授予复制权限
#在主服务器上创建用于复制的用户： [root@localhost ~]# mysql -uroot -p Enter password: #连接到主服务器的 MySQL 命令行界面。 #执行以下 SQL 语句创建复制用户，并授予复制权限： 用户名 从服务器的IP 密码 MariaDB [(none)]> CREATE USER 'rep'@'192.168.8.24' IDENTIFIED BY '000000'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.8.24'; Query OK, 0 rows affected (0.00 sec)
4、获取当前的二进制日志位置
#在主服务器上获取当前的二进制日志位置： MariaDB [(none)]> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 488 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) #记下结果中的 File 和 Position 值，这将在后续的步骤中使用.
2、从服务器（slave）
1、安装mysql
#1、安装mysql： [root@localhost ~]# yum -y install mariadb-server #2、重启mysql： [root@localhost ~]# systemctl restart mariadb #3、修改MySQL密码（000000） [root@localhost ~]# mysql_secure_installation
2、配置mysql文件
编辑从服务器的 MySQL 配置文件，启用中继日志（relay logging）功能 [root@localhost ~]# vim /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd #添加内容 relay-log=mysql-relay-bin server-id=2 #5、重启从服务器的 MySQL 服务，使配置生效 [root@localhost ~]# systemctl restart mariadb
3、设置主服务器的信息
设置主服务器的信息 [root@localhost ~]# mysql -uroot -p Enter password: MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.8.23', MASTER_USER='rep', MASTER_PASSWORD='000000', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=488; # MASTER_HOST为主库master的ip地址，MASTER_USER、MASTER_PASSWORD为1.3当中创建的用户名和 密码，MASTER_LOG_FILE、 MASTER_LOG_POS为步骤1.4当中查询到的两个值
4、启动从服务器的复制进程
MariaDB [(none)]> START SLAVE; Query OK, 0 rows affected (0.00 sec)
5、 检查复制状态：
MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.8.23 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 488 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 529 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes #状态为yes Slave_SQL_Running: Yes #状态为yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 488 Relay_Log_Space: 823 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.00 sec) #检查输出中的 Slave_IO_Running 和 Slave_SQL_Running 字段，确保两者的值都为 Yes，表示主从 搭建成功。
:3、测试：
#从服务器 MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | t1 | +--------------------+ 4 rows in set (0.00 sec) #主服务器创建一个数据库 MariaDB [(none)]> create database t2; Query OK, 1 row affected (0.00 sec) #从服务器查看 MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | t1 | | t2 | +--------------------+ 5 rows in set (0.00 sec) 能看见已经有't2'这个数据库了，就代表成功了
