mysql主从搭建

一、安装前准备
1.ip分配

2.目录结构

安装目录：/usr/local/mysql-5.7.28-linux-glibc2.12-x86_64
数据目录：/cust/data/mysql
日志目录：/cust/logs/mysql
备份目录：/cust/backup/mysql

3.增加用户

# useradd -M -s /sbin/nologin mysql

# -M 表示不创建用户家目录
# -s 指定用户登录 shell  /sbin/nologin 表示不登录

二、安装
1.下载软件

# cd /opt
# wget -nc https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
# tar zxf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz -C /usr/local/

2.创建相关目录 并修改所属主组给mysql用户

# mkdir -p /cust/data/mysql /cust/logs/mysql  /cust/backup/mysql
# chown -R mysql.mysql /usr/local/mysql-5.7.28-linux-glibc2.12-x86_64 /cust/data/mysql /cust/logs/mysql
# ln -s /usr/local/mysql-5.7.28-linux-glibc2.12-x86_64 /usr/local/mysql

3.添加环境变量

# echo 'export PATH=$PATH:/usr/local/mysql/bin/' >>/etc/profile
# source /etc/profile

4.修改配置文件 /etc/my.cnf

# cat /etc/my.cnf

[mysqld]
server-id = 104
#log_slave_updates=1
basedir=/usr/local/mysql             ###############
datadir=/cust/data/mysql             ###############  
pid-file=/cust/data/mysql/mysql.pid  ############### 
character-set-server = utf8mb4
default_storage_engine = innodb
socket=/tmp/mysql.sock
port=3306
skip_name_resolve = 1
max_allowed_packet = 64M
#innodb_buffer_pool_size按照linux中内存的60%～70%填写
innodb_buffer_pool_size = 3G         ###############
innodb_log_file_size = 256M
expire_logs_days = 14
innodb_file_per_table = 1
tmp_table_size = 64M
lower_case_table_names=1

sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

#log
log_error = /cust/logs/mysql/mysql_error.log        ###############
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /cust/logs/mysql/mysql_slow.log        ###############
log_bin = /cust/logs/mysql/mysql-bin
binlog_format = row


#connect
max_connections = 2000
max_connect_errors = 600
wait_timeout = 86400

# 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

[mysqld_safe]
log-error=/cust/logs/mysql/mysql_error.log		#############

[client]
socket=/tmp/mysql.sock

5.安装

# mysqld --initialize --datadir=/cust/data/mysql/ --user=mysql

6.启动 查看root密码 修改root密码 停止mysql

# 启动mysql
# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &

# 获取root密码
# echo `cat /cust/logs/mysql/mysql_error.log |grep root|awk -F ':' '{print $NF}' `| sed 's/ //g'

# 修改root密码
# mysql -uroot -p
# mysql> SET PASSWORD FOR 'root'@'localhost' = 'Sxg123456';

# 停止mysql
# mysqladmin -uroot  -P3306 -pSxg123456 shutdown   ## 关闭请执行此命令，安全系数比杀进程高

7.设置开机自启动

# pwd
/usr/local/mysql
# cp support-files/mysql.server /etc/init.d/mysqld   #然后将datadir 和basedir替换
# chmod +x /etc/init.d/mysqld 
# chkconfig --add mysqld
# chkconfig mysqld on

# /etc/init.d/mysqld restart    重启

三、主从设置

## 1.在Master上操作
## (1)创建用户 repl
mysql> grant replication slave on *.* to 'repl'@'%' identified by 'Sxg123456';

## (2)获取binlog文件以及位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |     154  |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

## (3)全库备份, 并发送到从库，正常来说 第一次部署之后不需要做数据迁移里面没有多少数据
# mysqldump -uroot -p --single-transaction --master-data=2 --no-autocommit -A -B > all.sql

## 2.在Slave上操做
## (1) 把主库数据恢复到从库
# mysqldump -uxxx -p --single-transaction --master-data=2 --no-autocommit -A > all_repl.sql

## (2)修改配置 /etc/my.cnf 在[mysqld]最后添加以下内容并重启
# slave
read_only=on		# 禁止写入数据 防止主从数据不一致
relay_log=/cust/logs/mysql/relay-log

# /etc/init.d/mysqld restart    重启

## (3)在命令行操作
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.43.104',MASTER_USER='repl',MASTER_PASSWORD='Sxg123456',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=154;
mysql> start slave;
mysql> show slave status\G			#关注 Slave_IO_Running   Slave_SQL_Running  这两个全部为 Yes

四、备份

五、其他
1.免密登录

# 第一种方式
# mysql_config_editor set --login-path=root --host=localhost --user=root --password --socket=/tmp/mysql.sock
# mysql --login-path=root   ## 登录

#第二种方式 修改配置文件 /etc/my.cnf   加 [client] 模块
[client]
socket=/tmp/mysql.sock
user=admin
password=Sxg123456

# mysql       ## 登录