Skip to main content

Mysql安装与初始化

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