Before Install
The following guide is based on Centos 6.7.
Centos的防火墙禁止mysql操作mysql配置路径之外的目录。所以如果要初始化在默认目录之外，需要将目录加入例外名单，或者关闭防火墙。
setenforce 0
Install Mysql
Binary
Download Here
Select the Linux-Generic to download the binary package.
Select the previous version of Mysql is at left side.
At this guide, we are using Mysql 5.6.31.
Download Linux - Generic (glibc 2.5) (x86, 64-bit), Compressed TAR Archive
wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.31-linux-glibc2.5-x86_64.tar.gz
Chech the Dependices
[root@tw bin]# ldd mysqld
linux-vdso.so.1 => (0x00007fffe01ba000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007ffe5711c000)
libaio.so.1 => /lib64/libaio.so.1 (0x00007ffe56f1b000)
librt.so.1 => /lib64/librt.so.1 (0x00007ffe56d12000)
libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007ffe56adb000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007ffe568d7000)
libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00007ffe565d0000)
libm.so.6 => /lib64/libm.so.6 (0x00007ffe5634c000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007ffe56136000)
libc.so.6 => /lib64/libc.so.6 (0x00007ffe55da1000)
/lib64/ld-linux-x86-64.so.2 (0x00007ffe5733f000)
libfreebl3.so => /lib64/libfreebl3.so (0x00007ffe55b28000)
[root@tw bin]#
Add User and Group
useradd mysql
Copy Mysql Files
tar xvzf mysql-5.6.31-linux-glibc2.5-x86_64.tar.gz
mkdir -p /usr/local/mysql
cd mysql-5.6.31-linux-glibc2.5-x86_64
cp -r * /usr/local/mysql
修改目录用户和用户组
chown -R mysql:mysql /usr/local/mysql/
配置环境变量(/etc/profile)
Append follow content to /etc/profile
export PATH=${PATH}:/usr/local/mysql/bin:/usr/local/mysql/scripts/
Make it activate
source /etc/profile
Yum
Install Yum Repo
rpm -ivh http://dev.mysql.com/get/mysql57-community-release-el6-8.noarch.rpm
Verify if Yum config manager exist
yum install yum-utils
Check the current yum mysql repo
yum repolist all | grep myql
If mysql-community yum is successful installed on the server, it should be displayed like the follow:
[root@preserver02 ~]# yum repolist all| grep mysql
mysql-connectors-community MySQL Connectors Community 启用: 21
mysql-connectors-community-source MySQL Connectors Community - Sourc 禁用
mysql-tools-community MySQL Tools Community 启用: 37
mysql-tools-community-source MySQL Tools Community - Source 禁用
mysql-tools-preview MySQL Tools Preview 禁用
mysql-tools-preview-source MySQL Tools Preview - Source 禁用
mysql55-community MySQL 5.5 Community Server 禁用
mysql55-community-source MySQL 5.5 Community Server - Sourc 禁用
mysql56-community MySQL 5.6 Community Server 禁用
mysql56-community-source MySQL 5.6 Community Server - Sourc 禁用
mysql57-community MySQL 5.7 Community Server 启用: 98
mysql57-community-source MySQL 5.7 Community Server - Sourc 禁用
[root@preserver02 ~]#
By Disable or enable the repo, use the follow command:
yum-config-manager --disable mysql57-community
yum-config-manager --enable mysql56-community
Install the Mysql:
yum install mysql-community-server mysql-community-client mysql-community-libs mysql-community-devel
Configure Mysql
Folder
Create folder for storage database file, log file and config, etc...
datadir /mysql/3306/
tmpdir /mysql/3306/tmp
binlog /mysql/3306/binlog/3306-binlog
errlog /mysql/3306/log/err.log
relaylog /mysql/3306/relaylog/3306-relay
slowquery /mysql/3306/slowlog/slow.log
config /mysql/3306/my.cnf
Folder Permission
chown -R mysql:mysql /mysql/
After chown -R, ls -al to confirm:
[root@t /]# ll
总用量 122
-rw-------. 1 root root 8192 7月 18 09:35 aquota.group
-rw-------. 1 root root 7168 7月 18 09:36 aquota.user
drwxr-xr-x. 2 root root 4096 7月 18 09:08 bak
dr-xr-xr-x. 2 root root 4096 7月 18 09:27 bin
dr-xr-xr-x. 5 root root 1024 7月 18 07:19 boot
drwxr-xr-x. 18 root root 3640 7月 18 07:33 dev
drwxr-xr-x. 94 root root 12288 7月 18 10:25 etc
drwxr-xr-x. 2 root root 4096 9月 23 2011 home
dr-xr-xr-x. 10 root root 4096 7月 18 07:17 lib
dr-xr-xr-x. 10 root root 12288 7月 18 09:42 lib64
drwx------. 2 root root 16384 7月 18 07:15 lost+found
drwxr-xr-x. 2 root root 4096 9月 23 2011 media
drwxr-xr-x. 2 root root 4096 9月 23 2011 mnt
drwxr-xr-x. 3 mysql mysql 4096 7月 18 10:27 mysql
drwxr-xr-x. 3 root root 4096 7月 18 07:17 opt
dr-xr-xr-x. 250 root root 0 7月 18 00:19 proc
dr-xr-x---. 4 root root 4096 7月 18 09:41 root
dr-xr-xr-x. 2 root root 12288 7月 18 09:27 sbin
drwxr-xr-x. 7 root root 0 7月 18 00:19 selinux
drwxr-xr-x. 2 root root 4096 9月 23 2011 srv
drwxr-xr-x. 13 root root 0 7月 18 00:19 sys
drwxrwxrwt. 6 root root 4096 7月 18 10:24 tmp
drwxr-xr-x. 13 root root 4096 7月 18 07:16 usr
drwxr-xr-x. 24 root root 4096 7月 18 09:29 var
[root@t /]#
create config file
Create the config file and save to /mysql/3306/my.cnf, the following config is based on mysql5.6.
DO NOT USE IT WITHOUT CHANGE ON MYSQL 5.7.
[mysqld]
# GENERAL #
user = mysql
port = 3306
socket = /mysql/3306/mysql.sock
pid_file = /mysql/3306/tmp/mysql.pid
datadir = /mysql/3306/data/
tmpdir = /mysql/3306/tmp
log_bin = /mysql/3306/binlog/3306-binlog
relay-log = /mysql/3306/relaylog/3306-relay
log_error = /mysql/3306/log/err.log
slow_query_log_file = /mysql/3306/slowlog/slow.log
long_query_time=1
sync_binlog = 0
expire_logs_days = 7
back_log=1024
skip-name-resolve
skip-slave-start
skip-external-locking
skip-character-set-client-handshake
explicit_defaults_for_timestamp=true
default_storage_engine = InnoDB
bind-address=0.0.0.0
#lower_case_table_names = 0
myisam_recover = FORCE,BACKUP
transaction-isolation = READ-COMMITTED
table_definition_cache = 4096
table_open_cache = 4096
# connection #
max_connections = 1100
max_user_connections = 1000
max_connect_errors = 1000
# timeout #
wait_timeout = 100
interactive_timeout = 100
lock_wait_timeout = 3
connect_timeout = 20
slave-net-timeout = 30
# character #
character-set-server=utf8
init-connect='SET NAMES utf8'
# disabled query cache #
query_cache_type = 0
query_cache_size = 0
# replication #
server_id=0
gtid_mode=ON
enforce-gtid-consistency
log-slave-updates
binlog-format=row
slave-parallel-workers=6
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync_master_info = 10000
slave_sql_verify_checksum=1
skip-slave-start
# session #
key_buffer_size = 128M
tmp_table_size = 32M
max_heap_table_size = 32M
max_allowed_packet = 32M
bulk_insert_buffer_size = 32M
sort_buffer_size = 128K
read_buffer_size = 1M
read_rnd_buffer_size = 1M
join_buffer_size = 128K
myisam_sort_buffer_size = 32M
tmp_table_size = 32M
max_heap_table_size = 64M
thread_cache_size = 64
#thread_concurrency = 32
thread_stack = 192K
# INNODB #
innodb_flush_method = O_DIRECT
innodb_data_home_dir = /mysql/3306/data/
innodb_data_file_path = ibdata1:100M:autoextend
#redo log
innodb_log_group_home_dir=/mysql/3306/data/
innodb_log_files_in_group = 3
innodb_log_file_size = 1G
#innodb performance
innodb_flush_log_at_trx_commit = 0
innodb_file_per_table = 1
innodb_buffer_pool_instances = 8
innodb_io_capacity = 2000
innodb_lock_wait_timeout = 30
binlog_error_action = ABORT_SERVER
innodb_buffer_pool_size = 256M
innodb_max_dirty_pages_pct=90
innodb_file_format=Barracuda
innodb_support_xa = 0
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1
Initalize Mysql Database
Notice that mysql 5.7 will generate a default password and log it into error log.
Mysql 5.6
mysql_install_db --defaults-file=/mysql/3306/my.cnf --user=mysql --basedir=/usr/local/bin/mysql
Mysql 5.7
mysqld --defaults-file=/mysql/3306/my.cnf --initialize --user=mysql
Start Mysql
Running mysql by mysqld
mysqld --defaults-file=/mysql/3306/my.cnf &
If it works, process will be shown:
[root@preserver02 log]# ps aux | grep mysql
mysql 25562 0.0 1.0 1293752 177232 pts/0 Sl 14:14 0:00 mysqld --defaults-file=/mysql/3306/my.cnf
root 25968 0.0 0.0 103264 848 pts/0 S+ 14:20 0:00 grep mysql
[root@preserver02 log]#
Connect and change password
[root@tw 3306]# mysql -u root -p -S /mysql/3306/my3306.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set password=password('xiaoCan!(91');
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
Stop Mysql
Stop mysql by mysqladmin
mysqladmin --defaults-file=/mysql/3306/my.cnf -S /mysql/3306/my3306.sock -u root -p shutdown
View Status
Check mysqlstatus by mysqladmin
[root@preserver02 log]# mysqladmin --defaults-file=/mysql/3306/my.cnf -S /mysql/3306/my3306.sock -u root -p ping
Enter password:
mysqld is alive
[root@preserver02 log]# mysqladmin --defaults-file=/mysql/3306/my.cnf -S /mysql/3306/my3306.sock -u root -p status
Enter password:
Uptime: 78 Threads: 1 Questions: 6 Slow queries: 0 Opens: 108 Flush tables: 1 Open tables: 101 Queries per second avg: 0.076
[root@preserver02 log]#
Multiple mysql servers start
mysqld or mysqld_safe
使用mysqld或者mysqldsafe启动多实例，实际上是初始化多个mysql数据库，然后在配置文件中分配不同的端口、server-id，然后使用mysqld或者mysqldsafe加载配置文件分别启动。
mysqld --defaults-file=/mysql/3307_fake/my.cnf &
mysqld --defaults-file=/mysql/3306_master/my.cnf &
此时，查询进程中可以发现存在两个mysqld的进程
[root@tw ~]# ps aux | grep mysql
mysql 92059 0.1 70.8 1627760 705624 pts/0 Sl 09:42 0:01 mysqld --defaults-file=/mysql/3306_master/my.cnf
mysql 92060 0.1 70.8 3585580 4604 pts/0 Sl 09:42 0:01 mysqld --defaults-file=/mysql/3307_fake/my.cnf
root 92253 0.0 0.0 103256 872 pts/1 S+ 09:53 0:00 grep mysql
[root@tw ~]#
mysqld_multi
配置
Mysqld Multi的方式启动Mysql多实例，需要将多个mysql的配置放在同一个配置文件中。每个mysqld配置命名使用mysqld+端口。格式如下：
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = 管理账号
pass = 管理密码
log = /mysql/multi.log
[mysqld端口]
配置，配置中必须指定mysqld所使用端口，且server-id不能相同
[mysqld端口]
配置，配置中必须指定mysqld所使用端口，且server-id不能相同
Example：
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = shutdownuser
pass = shutdown
log = /mysql/multi.log
[mysqld3307]
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
lower_case_table_names=1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
port=3306
user=mysql
[mysqld3307]
server-id=3307
log-bin=/mysql/sync/binlog
datadir=/mysql/data
socket=/mysql/tmp/mysql.sock
innodb_large_prefix = ON
innodb_file_format=barracuda
innodb_file_per_table = 1
lower_case_table_names = 1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/mysql/log/mysqld.log
pid-file=/mysql/tmp/mysqld.pid
max_allowed_packet = 3M
user=mysql
port=3307
collation_server=utf8mb4_unicode_ci
character_set_server=utf8mb4
权限
因为mysqld_multi使用配置文件中使用的user和password进行mysqld对应实例的管理，所以需要在其管理的实例中添加具有shutdown权限的用户，否则无法正常关闭管理的实例。 连接上mysql，创建用户和授权：
create user 'shutdownuser'@'localhost' identified by 'password'
grant shutdown on *.* to 'shutdownuser'@'localhost';
flush privileges;
Manage
mysqld_multi operation(start|stop|report) {mysql port which in the config file}
Start Mysql
mysqld_multi --defaults-file=/mysql/multi.cnf --user=mysql start 3306
mysqld_multi --defaults-file=/mysql/multi.cnf --user=mysql start 3307
Stop Mysql
mysqld_multi --defaults-file=/mysql/multi.cnf --user=mysql stop 3306
mysqld_multi --defaults-file=/mysql/multi.cnf --user=mysql stop 3307
Check Mysql status
mysqld_multi --defaults-file=/mysql/multi.cnf --user=mysql report 3306
mysqld_multi --defaults-file=/mysql/multi.cnf --user=mysql report 3307