一、安装前准备
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 ## 登录