CentOS update MySQL

# 准备

# 系统版本

more /etc/redhat-release

# repo网站 http://repo.mysql.com/

# 安装源

rpm -Uvh http://repo.mysql.com/mysql-community-release-el6.rpm

# 升级

yum upgrade mysql

# MySQL Secure Installation

/usr/bin/mysql_secure_installation

# 初始化

/usr/bin/mysql_install_db

# 修改/etc/my.cnf

[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
user=mysql
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

# 修改字符集
# (Don’t blindly copy-paste this! The exact statement depends on the column type, maximum length, and other properties. The above line is just an example for a VARCHAR column.)
# For each database:

ALTER DATABASE vipmonk CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# For each table:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# For each column:

ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# 批量修改所有表和字段的字符集

SELECT CONCAT('alter table ',table_name,' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM
    INFORMATION_SCHEMA.tables
WHERE
    TABLE_SCHEMA='database_name' 

# 开机启动

chkconfig mysql on
chkconfig --list | grep mysql

# MySQL默认安装

/var/lib/mysql/     #数据库目录
/usr/share/mysql    #配置文件目录
/usr/bin            #相关命令目录
/etc/init.d/mysql   #启动脚本
/var/log/mysqld.log #错误log文件

# 错误集锦

[ERROR] InnoDB: auto-extending data file ./ibdata1 is of a different size 640 pages (rounded down to MB) than specified in the .cnf file: initial 768 pages, max 0 (relevant if non-zero) pages!
# 解决

cd /var/lib/mysql
rm ibdata1 ib_logfile0 ib_logfile1

# 错误
[ERROR] InnoDB: ./ibdata1 can’t be opened in read-write mode
# 解决

chown -R mysql:mysql /var/lib/mysql

# Could not create the upgrade info file ‘/var/lib/mysql/mysql_upgrade_info’ in the MySQL Servers datadir, errno: 13

sudo mysql_upgrade

# Can’t find messagefile ‘/usr/share/mysql/errmsg.sys’

cp mysql/share/english/errmsg.sys /usr/share/mysql/errmsg.sys

# libmysqlclient.so.16: cannot open shared object file
# PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib/php/modules/mysql.so’ - libmysqlclient.so.16: cannot open shared object file: No such file or directory in Unknown on line 0
# PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib/php/modules/mysqli.so’ - libmysqlclient.so.16: cannot open shared object file: No such file or directory in Unknown on line 0
# PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib/php/modules/pdo_mysql.so’ - libmysqlclient.so.16: cannot open shared object file: No such file or directory in Unknown on line 0

sudo ln -s /usr/lib/mysql/libmysqlclient.so.16 /usr/lib/libmysqlclient.so.16

php -v