MySQL 5.7.26部署及授权，主从配置

将mysql-5.7.26上传到/home/mysql目录下

创建用户

groupadd mysql
useradd -g mysql mysql

设置用户操作系统资源的限制
# vim /etc/security/limits.conf
添加如下内容：
mysql   soft    nproc   2047
mysql   hard   nproc    16384
mysql   soft    nofile   1024
mysql   hard    nofile   65536
 

 将软件mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz上传到mysql目录中，上传完成后查看目录

tar -xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C /opt
ln -s /home/mysql/mysql-5.7.26-linux-glibc2.12-x86_64  /home/mysql/mysql
chown -R mysql.mysql /home/mysql/ mysql-5.7.26-linux-glibc2.12-x86_64/
chown -R mysql.mysql /home/mysql/mysql/
chmod  u+w /home/mysql/mysql
mkdir -p /home/mysql/mysqldata1
cd /home/mysql/mysqldata1
mkdir -p binlog  innodb_log  innodb_ts  log  mydata  relaylog  sock  tmpdir  scripts  pid  scripts
chown mysql:mysql /home/mysql/mysqldata1 -R   （改变user和group成为mysql）

配置环境变量
vim /home/mysql/.bash_profile
添加命令路径，往/etc/profile中添加下面的内容：

export MYSQL_HOME=/home/ump/mysql
export PATH=$MYSQL_HOME/bin:$PATH

source /home/mysql/.bash_profile

vi /etc/profile

export MYSQL_HOME=/home/ump/mysql
export PATH=$MYSQL_HOME/bin:$PATH

source /etc/profile
vim /etc/my.conf

[client] 
default-character-set = utf8mb4 
vim /etc/my.cnf
[mysql]
default-character-set=utf8mb4
[mysqld]
character-set-server = utf8mb4
default_password_lifetime=0
port=3306
server-id =1
basedir=/home/mysql/mysql
datadir=/home/mysql/mysqldata3/mydata
socket=/home/mysql/mysqldata3/sock/mysql.sock
skip-name-resolve = on 
lower_case_table_names = 1
log-error=/home/mysql/mysqldata3/log/mysqld_err.log
pid-file=/home/mysql/mysqldata3/pid/mysqld.pid
slow_query_log = 1
slow_query_log_file=/home/mysql/mysqldata3/log/slow-query.log
max_allowed_packet = 16M
innodb_lock_wait_timeout = 500
max_connections = 2048
sort_buffer_size = 16M
read_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
auto_increment_increment = 1   
auto_increment_offset  = 1  
tmp_table_size=512M
innodb_io_capacity = 800
skip_external_locking
log-bin-index=/home/mysql/mysqldata3/binlog/mysql-bin.index
log-bin=/home/mysql/mysqldata3/binlog/mysql-bin
log_bin_trust_function_creators=1
binlog_cache_size=2M
max_binlog_size=512M
binlog-format=ROW
sync_binlog=1
expire_logs_days=15
slave-skip-errors=1022,1032,1062
key_buffer_size=32M
innodb_data_home_dir=/home/mysql/mysqldata3/innodb_ts
innodb_data_file_path=ibdata1:200M:autoextend
innodb_log_group_home_dir=/home/mysql/mysqldata3/innodb_log
innodb_log_files_in_group=3
innodb_log_file_size=1024M
innodb_file_per_table=1
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances=8
innodb_log_buffer_size=8M
innodb_fast_shutdown=1
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=0
innodb_lock_wait_timeout = 500
innodb_io_capacity = 800
innodb_rollback_on_timeout=0
innodb_max_dirty_pages_pct=75
innodb_support_xa=OFF
transaction_isolation=READ-COMMITTED
innodb_read_io_threads=4
innodb_write_io_threads=16
innodb_file_format=barracuda
innodb_file_format_check=ON
innodb_strict_mode=1
max_connect_errors=99999
max_allowed_packet=64M
max_heap_table_size=256M
max_length_for_sort_data=16k
wait_timeout=3600
interactive_timeout=7200
table_open_cache=512
thread_cache_size=512
net_buffer_length=1M
read_rnd_buffer_size=512K
sort_buffer_size=16M
join_buffer_size=16M
query_cache_size=128M
query_cache_limit=8M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

初始化
cd /home/mysql/mysql/support-files
./scripts/mysql_install_db --defaults-file=/etc/my.cnf --user=mysql --datadir=/home/mysql/mysqldata1/mydata --basedir=/home/mysql/mysql --socket=/home/mysql/mysqldata1/sock/mysql.sock

修改密码
mysqladmin -uroot password 123456
/home/mysql/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &

创建数据库、授权用户
主库
create database app;
grant all on app.* to app@'ip' identified by 'qwe';
grant all on app.* to app@'ip' identified by 'qwe';
grant all on *.* to root@'localhost' identified by '123456';
grant all on *.* to root@'127.0.0.1' identified by '123456';

从库创建授权用户
grant all on app.* to app@'ip' identified by 'qwe';
grant all on app.* to app@'ip' identified by 'qwe';
grant all on *.* to root@'localhost' identified by '123456';
grant all on *.* to root@'127.0.0.1' identified by '123456';

创建业务用户
grant all on app.* to app@'%' identified by 'qwe';
数据库SQL导入
source /opt/1.sql

搭建主从复制
配置主库
CREATE USER 'master'@'ip' IDENTIFIED BY 'qwer'; 用于复制的账号
GRANT REPLICATION SLAVE ON *.* TO 'master'@'ip' IDENTIFIED BY 'qwer'; 授权
FLUSH PRIVILEGES; 刷新权限
show master status;
记录File 和Position 值

配置从库
修改vim /etc/my.cnf配置文件
将server-id=2 改成2

连接主库
CHANGE MASTER TO
  MASTER_HOST='ip',
  MASTER_USER='master',
  MASTER_PASSWORD=qwer,
  MASTER_PORT=3306,
  MASTER_LOG_FILE='File',
  MASTER_LOG_POS=Position;

开启主从复制
start slave;

查看同步状态
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes