Ubuntu 14.04，如果已经通过 ppa 的方式安装了 MySQL 5.6，所以首先得去掉这个源。
- sudo apt-add-repository --remove ppa:ondrej/mysql-5.6
- # 如果没有 apt-add-repository 先安装上
- # sudo apt-get install software-properties-common
手工删除的话可以去 /etc/apt/sources.list.d 目录下干掉类似 xxx_mysql-5.6_xxx.list 的文件即可。
然后再安装上官方 apt 源，先在 http://dev.mysql.com/downloads/repo/apt/ 下载最新的 deb 文件，然后使用 dpkg 命令添加源，最后执行安装 MySQL 命令即可：
- wget https://repo.mysql.com//mysql-apt-config_0.8.1-1_all.deb
- sudo dpkg -i mysql-apt-config_0.8.1-1_all.deb
- sudo apt-get update
- sudo apt-get install mysql-server
关于配置优化，介绍一个好网站：http://tools.percona.com/ (Free online productivity tools for MySQL DBAs, SysAdmins and Developers)。我 1 CPU，1 GB RAM 的配置如下：
- # Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
- # Configuration name iyaozhen.com generated for [email protected] at 2015-10-29 17:12:28
- #
- # The MySQL Community Server configuration file.
- #
- # For explanations see
- # http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
- [mysql]
- # CLIENT #
- port = 3306
- socket = /var/run/mysqld/mysqld.sock
- [mysqld_safe]
- pid-file = /var/run/mysqld/mysqld.pid
- socket = /var/run/mysqld/mysqld.sock
- nice = 0
- [mysqld]
- # GENERAL #
- user = mysql
- default-storage-engine = InnoDB
- pid-file = /var/run/mysqld/mysqld.pid
- socket = /var/run/mysqld/mysqld.sock
- port = 3306
- basedir = /usr
- datadir = /var/lib/mysql
- tmpdir = /tmp
- lc-messages-dir = /usr/share/mysql
- explicit_defaults_for_timestamp
- # Disabling symbolic-links is recommended to prevent assorted security risks
- symbolic-links = 0
- # Instead of skip-networking the default is now to listen only on
- # localhost which is more compatible and is not less secure.
- bind-address = 127.0.0.1
- # MyISAM #
- key-buffer-size = 32M
- # SAFETY #
- max-allowed-packet = 16M
- sysdate-is-now = 1
- skip-name-resolve
- # CACHES AND LIMITS #
- tmp-table-size = 32M
- max-heap-table-size = 32M
- query-cache-type = 0
- query-cache-size = 0
- max-connections = 500
- thread-cache-size = 50
- open-files-limit = 65535
- table-definition-cache = 1024
- table-open-cache = 2048
- # INNODB #
- innodb-flush-method = O_DIRECT
- innodb-log-files-in-group = 2
- innodb-log-file-size = 64M
- innodb-flush-log-at-trx-commit = 2
- innodb-file-per-table = 1
- innodb-buffer-pool-size = 768M
- # LOGGING #
- log-error = /var/log/mysql/error.log
- log-queries-not-using-indexes = 1
- slow-query-log = 1
- slow-query-log-file = /var/log/mysql/slow-query.log
我在启动的时候，查看 error.log 日志，发现了一些错误，下面是其解决方法：
[Warning] Could not increase number of max_open_files to more than 4096 (request: 65535)
解决办法：vim /etc/security/limits.conf，然后添加几行：
- mysql soft nofile 65535
- mysql hard nofile 65535
- www-data soft nofile 65535
- www-data hard nofile 65535
目的是增大文件打开数，提升一定的性能，适当即可。
plugin load error:
[Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
[ERROR] Function 'blackhole' already exists
[Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
[ERROR] Function 'federated' already exists
[Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
[ERROR] Function 'innodb' already exists
[Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
解决办法：看样子是这几个插件已经内置了，无需加载。查看 plugin 表里面也就这几个插件：
+-----------+-----------------+
| name | dl |
+-----------+-----------------+
| archive | ha_archive.so |
| blackhole | ha_blackhole.so |
| federated | ha_federated.so |
| innodb | ha_innodb.so |
+-----------+-----------------+
直接 truncate mysql.plugin 即可。
[Warning] CA certificate ca.pem is self signed.
解决办法：这个警告是因为使用了自签证书（猜测），即使开启传输加密也有被中间人攻击的风险，可以买个权威 CA 机构颁发的证书。我们这里就直接忽略这个错误了。
[Warning] ‘user’ entry ‘root@localhost’ ignored in –skip-name-resolve mode.
解决办法：这个可以忽略，因为我们设置 skip-name-resolve，禁用了 DNS 查询。（If you have a very slow DNS and many hosts, you might be able to improve performance either by disabling DNS lookups with –skip-name-resolve or by increasing the value of host_cache_size to make the host cache larger.）
启动的时候显示 “No directory, logging in with HOME=/”。
解决办法：这是 MySQL 的命令行日志，日志路径由环境变量 MYSQL_HISTFILE 指定。若系统中没有此环境变量，就会默认在 ~ （当前用户根目录）下生成 .mysql_history 隐藏文件，类似于 .bash_history，有一定的安全风险。如果你不想要这个文件，除了修改环境变量 MYSQL_HISTFILE 外，还有一个比较好的方式就是将文件软链接到 /dev/null（ln -s /dev/null $HOME/.mysql_history）。
没有评论