该文章总结一下MySQL的常见安装方式，以tar，yum，script 三种方式来演示：
一般的公司都会有自己统一的数据库安装规范和模板，在生产环境请按照自己的规范来安装和使用，这里只演示和测试，供需要的伙伴们参考
在文章中你会发现有的路径和指定的不一样，这都不影响，都可以自己折腾，毕竟dba在前期就要善于各种折腾，才能在关键时候：水来土掩....
1. 操作系统，系统环境，目结结构，用户，权限，日志路径，脚本
2. 配置规范化
1.1 操作系统准备
操作系统 Kylin Linux release 3.3.1707 (Core)
数据库版本 mysql-5.6.15-linux
mysql相关路径 /data/mysqldata/{3306/{data,tmp,binlog,slave,log/iblog},backup,scripts}
软件路径 /data/soft
备注：麒麟系统3.3，类似于Centos 7
1.2 相关软件准备
选择磁盘挂载目录/data作为mysql数据路径以及其他相关路径
[root@localhost keepalived]# cat /etc/redhat-release
Kylin Linux release 3.3.1707 (Core)
[root@localhost keepalived]# mkdir -p /data/soft
[root@localhost etc]# uname -a
Linux localhost.localdomain 3.10.0-514.ky3.kb3.x86_64 #1 SMP Thu Dec 29 21:29:54 EST 2016 x86_64 x86_64 x86_64 GNU/Linux
1.3 安装配置
1系统规划
Ip Hostname Role Server_id
192.168.19.223 mysql1 master 201801701
192.168.19.226 mysql2 master 201801702
[root@localhost ~]# lscpu
[root@localhost ~]# free -m #查看内存大小，后面根据该值修改innodb_buffer_pool_size
[root@localhost ~]# df -h #查看磁盘大小，看是否满足/data的要求
[root@localhost ~]# mkdir -p /data/soft #创建放软件的目录，并上传相应的安装包和软件
[root@localhost ~]# sysctl -a |grep swap #查看swap的值，如果是30，修改为10
vm.swappiness = 30
[root@localhost ~]# vim /etc/sysctl.conf #修改并保存（由于这里类似centos7，所以没必要全部关闭swap，避免OOM）
vm.swappiness = 10
[root@localhost ~]# sysctl -p
[root@localhost ~]# cat /sys/block/sda/queue/rotational #查看是ssd还是hdd
[root@localhost ~]# cat /sys/block/sda/queue/scheduler #查看磁盘调度方式
noop [deadline] cfq #hdd默认deadline比较合适，ssd选择noop,修改方式echo deadline > /sys/block/sda/queue/scheduler
[root@localhost ~]# date #查看2个系统的时间，时区,时间是否一致，要保持一致
[root@localhost ~]# hostname #查看并修改hostname
[root@localhost ~]# vim /etc/hosts
192.168.19.223 mysql1
192.168.19.226 mysql2
[root@localhost ~]# hostnamectl set-hostname mysql1 #另外一台mysql2
[root@localhost ~]# systemctl stop firewalld.service #关闭防火墙或者添加3306规则
[root@localhost ~]# systemctl disable firewalld.service
[root@localhost ~]# vim /etc/selinux/config #关闭selinux，并保存，一定要是disabled
SELINUX=disabled
[root@localhost ~]# vim /etc/profile #在文件中增加如下内容：方便在查看history的时候加上日期
HISTTIMEFORMAT="%Y:%M:%D %H-%m-%s"
export=HISTTIMEFORMAT
#设定时区
vim /etc/sysconfig/clock
ZONE=Asia/Shanghai
UTC=false
ARC=false
rm /etc/localtime
ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
# clock -w
[root@localhost ~]# reboot
配置yum源：
本地cd
[root@hqmysql1 dev]# ls -l /dev/cdrom |grep cdrom
lrwxrwxrwx. 1 root root 3 Jun 28 08:26 /dev/cdrom -> sr0
[root@rac1 ~]# mount -t iso9660 /dev/cdrom /mnt/
mount: block device /dev/sr0 is write-protected, mounting read-only
[root@hqmysql1 yum.repos.d]# vim public-yum-ol6.repo
[root@hqmysql1 yum.repos.d]# yum clean all
[root@hqmysql1 yum.repos.d]# cat public-yum-ol6.repo #这里用的oracle linux 6.3，其他环境修改类似地方
[ol6_latest]
name=Oracle Linux $releasever Latest ($basearch)
gpgkey=file:///mnt/RPM-GPG-KEY-oracle
baseurl=file:///mnt
gpgcheck=1
enabled=1
163 yum源：注意centos6/7
[root@mysql1slave yum.repos.d]# cat public-yum-ol6.repo
[base]
name=CentOS-$releasever - Base
#baseurl=http://mirrors.163.com/centos/$releasever/os/$basearch/
baseurl=http://mirrors.163.com/centos/6/os/x86_64/
gpgcheck=1
gpgkey=http://mirrors.163.com/centos/6/os/x86_64/RPM-GPG-KEY-CentOS-6
1.4 Mysql环境配置
[root@mysql1 ~]# getenforce
Disabled
[root@mysql1 ~]# groupadd mysql
[root@mysql1 ~]# useradd -g mysql mysql
[root@mysql1 ~]# passwd mysql #密码这里设置为mysql
[root@mysql1 ~]# vim /etc/security/limits.conf
mysql soft nproc 65535
mysql hard nproc 65535
mysql soft nofile 65535
mysql hard nofile 65535
[root@mysql1 ~]# ulimit -n 65535
[root@mysql1 ~]# vim /home/mysql/.bash_profile
export LANG=en_US.UTF-8
export PATH=/usr/local/mysql/bin:$PATH
export MYSQL_PS1="(\u@\h:\p) [\d]> "
tar 二进制安装
安装依赖包
[root@mysql1 ~]# yum -y install lrzsz
[root@mysql1 ~]# yum install -y gcc gcc-* make cmake gcc-c++ libaio libaio-devel bison bison-devel autoconf automake zlib* libxml* ncurses-devel libmcrypt* libtool-ltdl-devel* --skip-broken
[root@mysql1 ~]# yum install -y openssl openssl-devel ncurses ncurses-devel
创建mysql相应的目录
[root@mysql1 ~]# mkdir -p /data/mysqldata/{3306/{data,tmp,binlog,slave,log/iblog},backup,scripts}
[root@mysql1 backup]# cd /data/soft/
[root@mysql1 soft]# ll
-rw-r--r-- 1 root root 304382512 Jun 4 23:46 mysql-5.6.15-linux-glibc2.5-x86_64.tar.gz
解压并安装mysql
[root@mysql1 soft]# tar -zxvf mysql-5.6.15-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
[root@mysql1 soft]# cd /usr/local/
[root@mysql1 local]# ln -s mysql-5.6.15-linux-glibc2.5-x86_64 mysql
编辑配置文件my.cnf，详细内容见 tar_mysql_cnf.cnf
[root@mysql1 local]# touch /data/mysqldata/3306/my.cnf
[root@mysql1 soft]# mv tar_mysql_cnf.cnf /data/mysqldata/3306/my.cnf
初始化数据库
[mysql@mysql1 3306]$ /usr/local/mysql/scripts/mysql_install_db --defaults-file=/data/mysqldata/3306/my.cnf --datadir=/data/mysqldata/3306/data --basedir=/usr/local/mysql --user=mysql
Installing MySQL system tables...OK
Filling help tables...OK
修改文件目录权限
[root@mysql1 local]# chown -R mysql:mysql /usr/local/mysql/
[root@mysql1 local]# chown -R mysql:mysql /data/mysqldata/
启动mysql数据库
[mysql@mysql1 3306]$ /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysqldata/3306/my.cnf &
查看启动进程
[mysql@mysql1 3306]$ ps -ef|grep mysql
root 14878 12988 0 09:41 pts/0 00:00:00 su - mysql
mysql 14879 14878 0 09:41 pts/0 00:00:00 -bash
mysql 15140 14879 0 09:59 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysqldata/3306/my.cnf
mysql 16053 15140 0 09:59 pts/0 00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysqldata/3306/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysqldata/3306/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/data/mysqldata/3306/log/mysql-error.log --open-files-limit=65535 --pid-file=/data/mysqldata/3306/data/mysql1.pid --socket=/data/mysqldata/3306/mysql.sock --port=3306
root 16081 15022 0 09:59 pts/1 00:00:00 tail -f -n 100 mysql-error.log
mysql 16089 14879 0 10:00 pts/0 00:00:00 ps -ef
mysql 16090 14879 0 10:00 pts/0 00:00:00 grep --color=auto mysql
查看启动日志
[root@mysql1 log]# tail -f -n 100 /data/mysqldata/3306/log/mysql-error.log
2018-07-18 09:59:18 16679 [Note] Server socket created on IP: '::'.
2018-07-18 09:59:18 16679 [Warning] 'user' entry 'root@mysql2' ignored in --skip-name-resolve mode.
2018-07-18 09:59:18 16679 [Warning] 'user' entry '@mysql2' ignored in --skip-name-resolve mode.
2018-07-18 09:59:18 16679 [Warning] 'proxies_priv' entry '@ root@mysql2' ignored in --skip-name-resolve mode.
2018-07-18 09:59:18 16679 [Note] Event Scheduler: Loaded 0 events
2018-07-18 09:59:18 16679 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.15-log' socket: '/data/mysqldata/3306/mysql.sock' port: 3306 MySQL Community Server (GPL)
2018-07-18 09:59:18 16679 [Note] Event Scheduler: scheduler thread started with id 1
note:mysql 5.7 初始化和初次登录有所变化
初始化5.7
[mysql@mysql1 bin]$./mysqld --defaults-file=/data/mysqldata/3306/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysqldata/3306/data --user=mysql --initialize
查看初始化后随即生产的root@localhost密码
# cat /data/mysqldata/3306/log/mysql-error.log |grep "root@localhost"|awk -F " " '{print $11}'
[mysql@localhost bin]$ /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysqldata/3306/my.cnf &
[mysql@localhost bin]$ /usr/local/mysql/bin/mysql -uroot -p'BJ=u4XqsTR0h' -S /data/mysqldata/3306/mysql.sock
Yum 安装
这里yum安装后，修改了数据的指定路径，要求在安装后先修改my.cnf配置文件，在启动mysql，切记。
查看yum源看mysql的版本是否存在和一致
[root@mysql1 soft]# yum list|grep mysql
Repository gcc-4.9 is listed more than once in the configuration
Repository other is listed more than once in the configuration
mysql-community-client.x86_64 5.6.15-4.ky3 other
mysql-community-common.x86_64 5.6.15-4.ky3 other
mysql-community-devel.x86_64 5.6.15-4.ky3 other
mysql-community-embedded.x86_64 5.6.15-4.ky3 other
mysql-community-embedded-devel.x86_64
mysql-community-libs.x86_64 5.6.15-4.ky3 other
mysql-community-server.x86_64 5.6.15-4.ky3 other
mysql-community-test.x86_64 5.6.15-4.ky3 other
mysql-connector-odbc.x86_64 5.2.5-6.ky3.kb2 base
rpm -qa | grep mysql
yum list installed | grep mysql
创建相应mysql目录
[root@mysql1 ~]# mkdir -p /data/mysqldata/{3306/{data,tmp,binlog,slave,log/iblog},backup,scripts}
Yum安装mysql
[root@mysql1 ~]# yum install mysql-community-server -y
[root@mysql1 ~]# whereis mysql
mysql: /usr/bin/mysql /usr/lib64/mysql /usr/share/mysql /usr/share/man/man1/mysql.1.gz
[root@mysql1 ~]# which mysql
/usr/bin/mysql
编辑配置文件my.cnf，详细内容见 yum_mysql_cnf_my.cnf
[root@mysql1 local]# touch /data/mysqldata/3306/my.cnf
[root@mysql1 soft]# mv yum_mysql_cnf_my.cnf /data/mysqldata/3306/my.cnf
另外一台mysql，除了修改
server_id=2018071202 #避免双主出现自增冲突
innodb_buffer_pool_size #为实际内存的50%-70%
auto-increment-offset = 2 #避免双主出现自增冲突
[root@mysql1 ~]# chown -R mysql:mysql /data/mysqldata/
[root@mysql1 mysql]# systemctl start mysqld
[root@mysql1 mysql]# ps -ef|grep mysql
mysql 1022 1 0 04:37 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe
mysql 2007 1022 2 04:37 ? 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/data/mysqldata/3306/data --plugin-dir=/usr/lib64/mysql/plugin --log-error=/data/mysqldata/3306/log/mysql-error.log --open-files-limit=65535 --pid-file=/data/mysqldata/3306/data/mysql2.pid --socket=/data/mysqldata/3306/mysql.sock --port=3306
root 2050 2369 0 04:37 pts/0 00:00:00 grep --color=auto mysql
root 2580 2559 0 04:27 pts/1 00:00:00 su - mysql
mysql 2581 2580 0 04:27 pts/1 00:00:00 -bash
注意，yum安装的mysql的相关执行文件在路面 /usr/bin/ 下面
[mysql@mysql1 ~]$ ls /usr/bin/mysqld_safe
/usr/bin/mysqld_safe
查看启动日志
[root@mysql1 ~]# tail -n 100 /data/mysqldata/3306/log/mysql-error.log
yum安装后，可以用systemctl start mysqld 来快捷启动mysql，也可以使用脚本来操作mysql,
所有脚本里面涉及到/usr/local/mysql/bin/的地方全部替换为/usr/bin/
二进制安装与脚本安装的mysql的可执行文件的路径为/usr/local/mysql/bin/
yum安装后，mysql的可执行文件路径为/usr/bin/
脚本自动安装
注意：这里的脚本安装，实际上是二进制安装的一个封装版本，存在一定的风险，报错不容易处理，前提是要配置好yum源，与二进制安装后进行rpm打包的方式不一样
进入目录，查看相关文件和脚本
[root@mysql1 backup]# cd /data/soft/
-rwxr-xr-x 1 root root 6028 Jul 12 05:29 mysql_auto_glibc_56.sh
运行安装脚本，记得一定查看是否报错，如果有报错，修改后，重新运行脚本
[root@mysql1 backup]# ./mysql_auto_glibc_56.sh
查看启动日志
[root@mysql1 ~]# tail -n 100 /data/mysqldata/3306/log/mysql-error.log
初始化mysql账号环境
创建mysql账号
登录mysql数据库，并创建相关用户
[mysql@mysql1 3306]$ mysql -S /data/mysqldata/3306/mysql.sock
(root@localhost:mysql.sock) [(none)]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
(root@localhost:mysql.sock) [(none)]> delete from mysql.user where (user,host) not in(select 'root','localhost');
(root@localhost:mysql.sock) [(none)]> update mysql.user set password=password('mysql'); #修改root密码为mysql
(root@localhost:mysql.sock) [(none)]> flush privileges;
(root@localhost:mysql.sock) [(none)]> truncate table mysql.db; #清除该db内容
创建超级权限账号
(root@localhost:mysql.sock) [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'system'@'127.0.0.1' IDENTIFIED BY "mysql";
(root@localhost:mysql.sock) [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'system'@'10.15.%' IDENTIFIED BY "mysql";
(root@localhost:mysql.sock) [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'system'@'192.168.%' IDENTIFIED BY "mysql";
创建复制账号
(root@localhost:mysql.sock) [(none)]> GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* to repl@'192.168.%' identified by "onlyrepl";
创建备份账号
(root@localhost:mysql.sock) [(none)]> GRANT select,PROCESS,RELOAD,SUPER, REPLICATION CLIENT ON *.* to xtrabak@'127.0.0.1' IDENTIFIED BY "onlybackup"
(root@localhost:mysql.sock) [(none)]> flush privileges;
(root@localhost:mysql.sock) [(none)]> select user,host,password from mysql.user; #mysql 5.7 select user,host from mysql.user
+---------+-----------+-------------------------------------------+
| user | host | password |
+---------+-----------+-------------------------------------------+
| root | localhost | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| repl | 192.168.% | *B719E0A3A0C94CB80E0674E7160DE02AD7BC7A4E |
| system | 127.0.0.1 | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| xtrabak | 192.168.% | *4365F9BCD21C99F2CECBA43D73D074548F6CC864 |
+---------+-----------+-------------------------------------------+
#生产环境严格用户权限，密码控制（选择执行）
#grant select ,insert ,update,delete,execute on test.* to 'test_prog'@'%'identified by "D35DIK4296TD258" ;
#flush privileges;
#show grants for test_prog@'%';
#revoke insert ,update,delete,execute on test.* from test_prog@'%';
#grant insert ,update,delete,execute on test.* to 'test_prog'@'%';
常用脚本：（将在其他文章中展示）
[mysql@mysqlhq scripts]$ ll
total 44
-rw-rw-r-- 1 mysql mysql 0 Sep 19 10:18 0
-rwxr-xr-x 1 mysql mysql 498 Sep 19 10:18 auto_del_7_days_dbbk_yhq.sh
-rwxrwxr-x 1 mysql mysql 648 Sep 19 10:18 check_mysql_status_3306.sh
-rwxrwxr-x 1 mysql mysql 296 Sep 19 10:18 check_slave_status_3306.sh
-rwxr-xr-x 1 mysql mysql 5115 Sep 19 10:18 mysql_backup_dump_xtra.sh
-rwxr-xr-x 1 mysql mysql 267 Sep 19 10:18 mysql_db_shutdown.sh
-rwxr-xr-x 1 mysql mysql 267 Sep 19 10:18 mysql_db_startup.sh
-rwxr-xr-x 1 mysql mysql 2109 Sep 19 10:18 mysqldump_per_db.sh
-rw-r--r-- 1 mysql mysql 124 Sep 19 10:18 mysql_env.ini
-rwxr-xr-x 1 mysql mysql 292 Sep 19 10:18 mysqlplus.sh
-rwxrwxr-x 1 mysql mysql 2418 Sep 19 10:18 pt_table_checksum_mysql.sh
加入开机启动：
[root@hongquan1 ]# cat /etc/rc.d/rc.local
touch /var/lock/subsys/local
#mysql auto startup
sudo -i -u mysql /data/mysqldata/scripts/mysql_db_startup.sh > /home/mysql/mysql_db_startup.log 2>&1
#zabbix auto startup
/usr/local/zabbix/sbin/zabbix_server -c /usr/local/zabbix/etc/zabbix_server.conf
/usr/local/zabbix/sbin/zabbix_agentd -c /usr/local/zabbix/etc/zabbix_agentd.conf
#toku,mongodb3.6
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
#date ntpdate
/usr/sbin/ntpdate -u 182.92.12.11 ; /sbin/hwclock -w
my.cnf #5.6
#注意my.cnf的路径/home/data/mysqldata/3306
#上面安装的路径是/data/mysqldata/3306，
[mysql@mysqlhq scripts]$ cat /home/data/mysqldata/3306/my.cnf
[client]
port=3306
socket=/home/data/mysqldata/3306/mysql.sock
[mysql]
prompt = [\\u@\\p][\\d]>\\_
no-auto-rehash
pid_file=/home/data/mysqldata/3306/mysqld.pid
[mysqldump]
single-transaction
[mysqld]
autocommit=1
general_log=off
explicit_defaults_for_timestamp=true
#character set
character-set-server = utf8
event_scheduler = 1
#auto-increment-increment = 2
#auto-increment-offset = 1
# session memory settings #
read_buffer_size = 16M
read_rnd_buffer_size = 32M
sort_buffer_size = 32M
tmp_table_size = 32M
max_heap_table_size = 32M
join_buffer_size = 16M
thread_cache_size = 150
key_buffer_size = 32M
# system
#basedir=/usr/local/mysql
datadir=/home/data/mysqldata/3306/data
max_allowed_packet=32M
max_connections=3000
max_user_connections=2800
max_connect_errors = 1000000
open_files_limit=65535
lock_wait_timeout=120
query_cache_type=0
query_cache_size=0
#lower_case_table_names=1
port=3306
server_id=2018091901
skip_name_resolve=ON
socket=/home/data/mysqldata/3306/mysql.sock
tmpdir=/home/data/mysqldata/3306/tmp
#binlog
log_bin=/home/data/mysqldata/3306/binlog/mysql-bin
binlog_cache_size=32K
binlog_format=row
#expire_logs_days=90
log_slave_updates=ON
max_binlog_cache_size=4G
max_binlog_size=500M
sync_binlog=1
#transaction-isolation=READ-COMMITTED
#logging
log_error=/home/data/mysqldata/3306/log/mysql-error.log
slow_query_log_file=/home/data/mysqldata/3306/log/mysql-slow.log
log_queries_not_using_indexes=0
slow_query_log=1
log_slave_updates=ON
log_slow_admin_statements=1
long_query_time=2
log-bin-trust-function-creators = 1
#relay
relay_log=/home/data/mysqldata/3306/slave/relaylog
relay_log_index=/home/data/mysqldata/3306/slave/mysqld-relay-bin.index
relay_log_info_file=/home/data/mysqldata/3306/slave/relay-log.info
#slave
slave_load_tmpdir=/home/data/mysqldata/3306/tmp
slave_skip_errors=1032,1062
skip-slave-start
#gitd
#gtid_mode = on
#enforce_gtid_consistency = 1
#innodb
innodb_data_home_dir=/home/data/mysqldata/3306/log/iblog
innodb_log_group_home_dir=/home/data/mysqldata/3306/log/iblog
innodb_adaptive_flushing=ON
innodb_adaptive_hash_index=ON
innodb_autoinc_lock_mode=2
#innodb_buffer_pool_instances=8
#innodb_thread_concurrency=16
#default
innodb_change_buffering=inserts
innodb_checksums=ON
innodb_buffer_pool_size= 6G
innodb_data_file_path=ibdata1:1G:autoextend
innodb_doublewrite=ON
innodb_file_format=Barracuda
innodb_file_per_table=ON
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_io_capacity=1000
innodb_lock_wait_timeout=10
innodb_log_buffer_size=200M
innodb_log_file_size=1G
innodb_log_files_in_group=4
innodb_max_dirty_pages_pct=60
innodb_open_files=60000
innodb_purge_threads=1
innodb_stats_on_metadata=OFF
innodb_support_xa=ON
innodb_use_native_aio=OFF
#innodb_write_io_threads = 16
#innodb_read_io_threads = 16
[mysqld_safe]
datadir=/home/data/mysqldata/3306/data