[TOC]

mysql 国内镜像

http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/
https://dev.mysql.com/downloads/mysql/5.6.html#downloads

# 下载完成后进行MD5校验和mysql官网进行对比,检查软件包是否被修改

$ md5sum mysql-5.6.37-linux-glibc2.12-x86_64.tar.gz 
16be12598f7e4dcedb91b79b133fdbfc  mysql-5.6.37-linux-glibc2.12-x86_64.tar.gz



http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/MySQL-5.7/mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz


$md5sum mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz
dbe7e5e820377c29d8681005065e5728  mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz

版本选择

  • 略

MySQL官方页面

https://dev.mysql.com

安装

安装前

CentOs 删除默认安装的mariadbcentos7
$ rpm -qa | grep mariadb
mariadb-libs-5.5.52-1.el7.x86_64
$ sudo yum  remove mariadb-libs
# 安装依赖软件
$ yum install libaio perl autoconf

5.6.x安装

https://dev.mysql.com/doc/refman/5.6/en/binary-installation.html

[root@mysqlenv_test ~]# mv mysql-5.6.37-linux-glibc2.12-x86_64.tar.gz /usr/local/
shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql
shell> cd /usr/local
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> chown -R mysql .
shell> chgrp -R mysql .
shell> scripts/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql data
shell> bin/mysqld_safe --user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server
shell> echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh   && cat /etc/profile.d/mysql.sh

5.7.x安装

官方安装文档
https://dev.mysql.com/doc/refman/5.7/en/binary-installation.html

[root@mysqlenv_test ~]# mv mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz /usr/local/
shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql
shell> cd /usr/local
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> mkdir mysql-files
shell> chmod 750 mysql-files
shell> chown -R mysql .
shell> chgrp -R mysql .
shell> bin/mysql_install_db --user=mysql    # MySQL 5.7.5
shell> bin/mysqld --initialize --user=mysql # MySQL 5.7.6 and up
shell> bin/mysql_ssl_rsa_setup              # MySQL 5.7.6 and up
shell> chown -R root .
shell> chown -R mysql data mysql-files
shell> bin/mysqld_safe --user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server 
shell> echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh   && cat /etc/profile.d/mysql.sh

验证是否安装成功

  • 指定的datadir是否为空
  • 查看datadir中的$HOSTNAME.err内容，
  • 5.7会生成临时的密码到error日志中，登录MySQL后及时修改密码set password = ("new_password");

启动

  • bin/mysqld_safe --user=mysql直接启动， mysqld_safe守护进程防止mysqld意外停止，当mysqld意外停止时mysqld_safe会重启mysqld进程
  • 使用/etc/init.d/mysql.server start进行启动（注意脚本的名字）

配置文件

# cat /etc/my.cnf 
[client]

[mysqld]

########basic settings########

server-id = 1 
port = 3306
user = mysql
# bind_address = 127.0.0.1   #根据实际情况修改
# autocommit = 0   #5.6.X安装时，需要注释掉，安装完成后再打开
character_set_server=utf8 # 或者 utf8mb4
# skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
datadir = /data/mysql_data      #根据实际情况修改,建议和程序分离存放
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
join_buffer_size = 134217728
tmp_table_size = 67108864
tmpdir = /tmp
max_allowed_packet = 16777216
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16777216
read_rnd_buffer_size = 33554432
sort_buffer_size = 33554432

########log settings########

log_error = error.log
slow_query_log = 1
slow_query_log_file = slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
long_query_time = 2
min_examined_row_limit = 100

########replication settings########

master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin = bin.log
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
binlog_format = row 
relay_log = relay.log
relay_log_recovery = 1
binlog_gtid_simple_recovery = 1
slave_skip_errors = ddl_exist_errors

########innodb settings########

innodb_page_size = 8192
innodb_buffer_pool_size = 1G    #根据实际情况修改
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_log_group_home_dir = /redolog/  #根据实际情况修改
innodb_undo_directory = /undolog/      #根据实际情况修改
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_flush_neighbors = 1
innodb_log_file_size = 1G               #根据实际情况修改
innodb_log_buffer_size = 16777216
innodb_purge_threads = 4                #根据实际情况修改
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 67108864 

########semi sync replication settings########

plugin_dir=/usr/local/mysql/lib/plugin      #根据实际情况修改
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

[mysqld-5.7]
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
transaction_write_set_extraction=MURMUR32
show_compatibility_56=on
  • 关注重要的参数
    • innodb_log_file_size = 2G 生产环境按实际情况修改
    • innodb_undo_logs = 128 和 innodb_undo_tablespaces = 3 安装之前先确定好，安装后不好修改
    • [mysqld]，[mysqld-5.7]:[mysqld]配置在所有MySQL版本全部生效，[mysqld-5.7]只在MySQL5.7.X版本下才生效
    • autocommit,这个参数在5.5.X以后才有，安装5.6.X前先把该参数注释掉，等安装完成后，再行打开, 5.7.X无需预先注释
    • datadir, innodb_log_group_home_dir, innodb_undo_directory一定要注意目录权限是 mysql:mysql
  • my.cnf关注点
    • mysqld --help -vv | grep my.cnf查看配置文件的读取顺序，如果有相同的配置，最后读取的文件配置项生效
    • 使用--defaults-files可指定配置文件

升级

  • 说明
    通常情况下使用MySQLLinux - Generic通用的二进制包进行安装，my.cnf中datadir指定数据目录;数据目录和程序目录分开后，对后续升级操作很方便
  • 例子

应用程序目录

5.6应用程序所在目录: /usr/local/mysql-5.6.37-linux-glibc2.12-x86_64
5.7应用撤销所在目录: /usr/local/mysql-5.7.19-linux-glibc2.12-x86_64

数据目录

/data/mysql_data/

环境


[root@mysqlenv_test /usr/local]#ls -l | grep mysql
lrwxrwxrwx   1 root  root   35 Sep 16 15:20 mysql -> mysql-5.6.37-linux-glibc2.12-x86_64 # 当前软链接指向为5.6的版本
drwxr-xr-x  13 mysql mysql 223 Sep 16 14:06 mysql-5.6.37-linux-glibc2.12-x86_64
drwxr-xr-x  10 root  mysql 148 Sep 16 13:25 mysql-5.7.19-linux-glibc2.12-x86_64

[root@mysqlenv_test ~]#ls -l  /data/mysql_data/
total 13576
-rw-rw---- 1 mysql mysql       56 Sep 16 15:22 auto.cnf
-rw-rw---- 1 mysql mysql    65423 Sep 16 15:21 bin.000001
-rw-rw---- 1 mysql mysql  1199276 Sep 16 15:21 bin.000002
-rw-rw---- 1 mysql mysql      369 Sep 16 15:29 bin.000003
-rw-rw---- 1 mysql mysql      356 Sep 16 15:35 bin.000004
-rw-rw---- 1 mysql mysql       52 Sep 16 15:30 bin.index
drwx------ 2 mysql mysql       20 Sep 16 15:31 db56
-rw-rw---- 1 mysql mysql    19134 Sep 16 15:35 error.log
-rw-rw---- 1 mysql mysql      865 Sep 16 15:35 ib_buffer_pool
-rw-rw---- 1 mysql mysql 12582912 Sep 16 15:35 ibdata1
drwx------ 2 mysql mysql     4096 Sep 16 15:21 mysql
drwx------ 2 mysql mysql     4096 Sep 16 15:21 performance_schema
-rw-rw---- 1 mysql mysql      350 Sep 16 15:30 slow.log
drwx------ 2 mysql mysql        6 Sep 16 15:21 test




Database changed
mysql> SELECT VERSION ();
+------------+
| VERSION () |
+------------+
| 5.6.37-log |
+------------+
1 row in set (0.00 sec)

mysql> CREATE DATABASE db56;
Query OK, 1 row affected (0.02 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db56               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

升级操作
应该在slave先进行测试

# 1. 必须安全停止数据库
[root@mysqlenv_test ~]#/etc/init.d/56mysql.server stop
Shutting down MySQL.. SUCCESS! 

# 2. 使用新的版本
[root@mysqlenv_test ~]#cd /usr/local/
[root@mysqlenv_test /usr/local]#unlink mysql
[root@mysqlenv_test /usr/local]#ln -sv mysql-5.7.19-linux-glibc2.12-x86_64 mysql
‘mysql’ -> ‘mysql-5.7.19-linux-glibc2.12-x86_64’
        # MySQL的应用程序版本已经升级完成
        #都不需要做任何的改变，即可将当前系统的mysql版本升级完成
        #注意：此时只是应用程序升级完成，系统表仍然还是5.6的版本

# 3.修改目录权限
[root@mysqlenv_test /usr/local]#cd /usr/local/mysql
[root@mysqlenv_test /usr/local/mysql]# chown root:mysql -R  . 

# 4.启动
[root@mysqlenv_test /usr/local]#/etc/init.d/mysql.server start
Starting MySQL....... SUCCESS! 
	# 此时error.log中有很多[ERROR] 日志或者警告日志
	# 需要进行upgrade更新系统表元数据才不会产生警告

# 5.升级元数据
[root@mysqlenv_test /usr/local]#mysql_upgrade -p -s  
Enter password: 
The --upgrade-system-tables option was used, databases won't be touched.
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Upgrading the sys schema.
Upgrade process completed successfully.
Checking if update is needed.
        # 参数 -s 一定要加,表示只更新系统表，-s: upgrade-system-tables
        # 如果不加-s,则会把所有库的表以5.7.x的方式重建，会非常的耗时
        # 因为数据库二进制文件是兼容的，无需升级


# 6. 验证
mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.7.19-log |
+------------+
1 row in set (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db56               |
| mysql              |
| performance_schema |
| sys                | # sys是5.7新加的库
| test               | # test在5.7以后已弃用，从5.6升级所以保留
+--------------------+
6 rows in set (0.00 sec)

注意
能否顺利升级datadir是关键，datadir最好是和程序目录进行分离