Ubuntu 下源码编译 Mysql

Summary: Author: 张亚飞 | 阅读时间: 16 minute read | Published: 2016-08-08
Filed under Categories: DevOpsTags: Linux, Server, Software, DevOps,

安装 MySQL

最简单的安装方式

sudo aptitude update
sudo aptitude install mysql-server

Mysql 配置文件: /etc/mysql/my.cnf

常用管理命令

sudo service mysql start
sudo service mysql stop
sudo service mysql restart

参考列表


源码编译安装最新 Mysql

首先卸载 apt 安装的 mysql

主要包括三个程序文件

mysql-client – The latest version of MySQL database client.
mysql-server – The latest version of MySQL database server.
mysql-common – MySQL database common files.
sudo apt --purge remove mysql-client mysql-server mysql-common
sudo apt autoremove

为了能够正常编译和安装MySQL,最好把通过命令安装的删除. 并且删除 /etc/mysql 文件夹.


安装编译源码需要的包

Ubuntu

sudo apt install build-essential libncurses5-dev
sudo apt install make cmake gcc g++ bison libncurses5-dev
sudo apt install libssl-dev
  1. CMake 官网
  2. GCC: A working ANSI C++ compiler. GCC 4.2.1 or later 官网
  3. bison,2.1 or newer 官网
  4. m4 : 官网
  5. tar : 官网

下载 boost 库

mkdir -p /usr/local/boost
cd /usr/local/boost
wget https://sourceforge.net/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz
tar zxvf boost_1_59_0.tar.gz

第一步,添加 mysql 用户

groupadd mysql
useradd -r -g mysql mysql

下载最新的源码包

MySQL5.7.12

sudo wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.15.tar.gz
sudo wget https://github.com/mysql/mysql-server/archive/mysql-5.7.16.tar.gz
sudo wget https://github.com/mysql/mysql-server/archive/mysql-5.7.18.tar.gz
sudo wget https://github.com/mysql/mysql-server/archive/mysql-5.7.22.tar.gz
tar zxvf mysql-5.7.22.tar.gz

第三步,执行编译选项:

MySQL自5.5版本以后,就开始使用 CMake 编译工具了,因此,你在安装源文件中找不到configure文件是正常的.很多人下到了新版的MySQL,因为找不到 configure 文件,不知道该怎么继续下去. 有没有一篇可供参考的文章呢?其实在 http://forge.mysql.com 网站上有一篇文章,专门介绍了如何用CMake工具进行新版MySQL的编译安装.

cd mysql-5.7.22/
mkdir build
cd build
sudo cmake .. -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
    -DMYSQL_DATADIR=/data/home/coam/data/mysql \
    -DSYSCONFDIR=/etc/mysql \
    -DMYSQL_TCP_PORT=3306 \
    -DMYSQL_UNIX_ADDR=/var/run/mysqld/mysqld.sock \
    -DINSTALL_LIBDIR=/usr/lib/mysql \
    -DINSTALL_PLUGINDIR=/usr/lib/mysql/plugin \
    -DWITH_SYSTEMD=1 \
    -DENABLE_DOWNLOADS=1 \
    -DENABLE_DOWNLOADS=1 \
    -DDOWNLOAD_BOOST=1 \                ====> 加上 boost 选项
    -DWITH_BOOST=/usr/local/boost \     ====> 加上 boost 选项
    -DWITH_EMBEDDED_SERVER=1 \
    -DENABLED_LOCAL_INFILE=1 \
    -DWITH_INNOBASE_STORAGE_ENGINE=1 \
    -DWITH_EXTRA_CHARSETS=complex \
    -DWITH_PARTITION_STORAGE_ENGINE=1 \
    -DWITH_FEDERATED_STORAGE_ENGINE=1 \
    -DWITH_MYISAM_STORAGE_ENGINE=1 \
    -DWITH_ARCHIVE_STORAGE_ENGINE=1 \
    -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
    -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
    -DDEFAULT_CHARSET=utf8 \
    -DDEFAULT_COLLATION=utf8_general_ci \
    -DEXTRA_CHARSETS=all \
    -DWITH_SSL=system \
    -DWITH_READLINE=1 \
    -DENABLE_PROFILING=0

-DWITH_EXTRA_CHARSETS:STRING=utf8,gbk
由于 -DWITH_DEBUG 默认就是 OFF 状态,所以也无需特别指定此参数.

  • 说明:是为了把 mysql 安装到 ~/usr/local/mysql 路径下
CMake Error: The following variables are used in this project, but they are set to NOTFOUND.
Please set them or make sure they are set and tested correctly in the CMake files:
OPENSSL_INCLUDE_DIR
   used as include directory in directory /opt/data/mysql-5.7.16/build/CMakeFiles/CMakeTmp
   used as include directory in directory /opt/data/mysql-5.7.16/build/CMakeFiles/CMakeTmp
   used as include directory in directory /opt/data/mysql-5.7.16/build/CMakeFiles/CMakeTmp

sudo apt install libssl-dev

注:上述 cmake 编译出现如下错误:

CMake Error at cmake/boost.cmake:81 (MESSAGE):
  You can download it with -DDOWNLOAD_BOOST=1 -DWITH_BOOST=<directory>  =============> 需要boost支持,这一点跟5.6版本的不一样

  This CMake script will look for boost in <directory>.  If it is not there,
  it will download and unpack it (in that directory) for you.

  If you are inside a firewall, you may need to use an http proxy:

  export http_proxy=http://example.com:80

Call Stack (most recent call first):
  cmake/boost.cmake:238 (COULD_NOT_FIND_BOOST)
  CMakeLists.txt:451 (INCLUDE)


-- Configuring incomplete, errors occurred!
See also "/opt/data/mysql-5.7.12/CMakeFiles/CMakeOutput.log".

重新按照要求加上 boost 选项,再次cmake:

cmake ...
-DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=/usr/local/boost \
...
  • 附编译过程
coam@coam:/opt/data/mysql-5.7.12$ sudo cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
>     -DMYSQL_DATADIR=/data/home/coam/data/mysql \
>     -DSYSCONFDIR=/etc/mysql \
>     -DMYSQL_TCP_PORT=3306 \
>     -DMYSQL_UNIX_ADDR=/var/run/mysqld/mysqld.sock \
>     -DINSTALL_LIBDIR=/usr/lib/mysql \
>     -DINSTALL_PLUGINDIR=/usr/lib/mysql/plugin \
>     -DENABLE_DOWNLOADS=1 \
>     -DDOWNLOAD_BOOST=1 \
>     -DWITH_BOOST=/usr/local/boost \
>     -DWITH_EMBEDDED_SERVER=1 \
>     -DENABLED_LOCAL_INFILE=1 \
>     -DWITH_INNOBASE_STORAGE_ENGINE=1 \
>     -DWITH_EXTRA_CHARSETS=complex \
>     -DWITH_PARTITION_STORAGE_ENGINE=1 \
>     -DWITH_FEDERATED_STORAGE_ENGINE=1 \
>     -DWITH_MYISAM_STORAGE_ENGINE=1 \
>     -DWITH_ARCHIVE_STORAGE_ENGINE=1 \
>     -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
>     -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
>     -DDEFAULT_CHARSET=utf8 \
>     -DDEFAULT_COLLATION=utf8_general_ci \
>     -DEXTRA_CHARSETS=all \
>     -DWITH_SSL=system \
>     -DWITH_READLINE=1 \
>     -DENABLE_PROFILING=0
-- Running cmake version 2.8.12.2
-- Configuring with MAX_INDEXES = 64U
-- SIZEOF_VOIDP 8
-- MySQL 5.7.12
-- Packaging as: mysql-5.7.12-Linux-x86_64
-- Downloading boost_1_59_0.tar.gz to /usr/local/boost
-- [download 0% complete]
-- [download 1% complete]
-- [download 2% complete]
-- [download 3% complete]
...                                                                                              ===============> 如果出现错误 :-- Download failed, error: 28;"Timeout was reached" //实在是受不了这蜗牛的速度,大概30Kb/s 所以下载失败
-- [download 97% complete]
-- [download 98% complete]
-- [download 99% complete]
-- [download 100% complete]
-- cd /usr/local/boost; tar xfz /usr/local/boost/boost_1_59_0.tar.gz            ======> //建议复制链接,直接浏览器上,或者迅雷上下载,速度超快
-- Found /usr/local/boost/boost_1_59_0/boost/version.hpp
-- BOOST_VERSION_NUMBER is #define BOOST_VERSION 105900
-- BOOST_INCLUDE_DIR /usr/local/boost/boost_1_59_0
-- Performing Test HAVE_LLVM_LIBCPP
-- Performing Test HAVE_LLVM_LIBCPP - Failed
-- Looking for include file pthread.h
-- Looking for include file pthread.h - found
-- Looking for pthread_create
-- Looking for pthread_create - not found
-- Looking for pthread_create in pthreads
-- Looking for pthread_create in pthreads - not found
-- Looking for pthread_create in pthread
-- Looking for pthread_create in pthread - found
-- Found Threads: TRUE
-- Looking for floor
-- Looking for floor - not found
...
-- Searching 16 bit integer
-- Using unsigned short
-- Check if the system is big endian - little endian
-- Found ZLIB: zlib (found version "1.2.3")
-- Download failed, error: 7;"Couldn't connect to server"
-- To enable google test, please download http://googlemock.googlecode.com/files/gmock-1.7.0.zip to the directory /opt/data/mysql-5.7.12/source_downloads
-- If you are inside a firewall, you may need to use an http proxy: export http_proxy=http://example.com:80
-- Performing Test HAVE_UNUSED_TYPEDEFS
-- Performing Test HAVE_UNUSED_TYPEDEFS - Success
-- Library mysqlserver depends on OSLIBS -lpthread;m;rt;/usr/lib/x86_64-linux-gnu/libssl.so;/usr/lib/x86_64-linux-gnu/libcrypto.so;dl;crypt
-- INSTALL mysqlclient.pc /usr/lib/pkgconfig
-- CMAKE_BUILD_TYPE: RelWithDebInfo
-- COMPILE_DEFINITIONS: _GNU_SOURCE;_FILE_OFFSET_BITS=64;HAVE_CONFIG_H
-- CMAKE_C_FLAGS:  -Wall -Wextra -Wformat-security -Wvla -Wwrite-strings -Wdeclaration-after-statement
-- CMAKE_CXX_FLAGS:  -Wall -Wextra -Wformat-security -Wvla -Woverloaded-virtual -Wno-unused-parameter
-- CMAKE_C_FLAGS_RELWITHDEBINFO: -O3 -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF
-- CMAKE_CXX_FLAGS_RELWITHDEBINFO: -O3 -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF
-- Configuring done
-- Generating done
-- Build files have been written to: /opt/data/mysql-5.7.12
  • 查看下载好 boost 文件
coam@coam:/usr/local/boost$ ls -al                                        =====> //目录要和 cmake 定义的一致
total 81768
drwxr-xr-x  3 root root     4096 May 11 18:56 .
drwxr-xr-x 12 root root     4096 May 11 18:51 ..
drwx------  8 root root     4096 May 11 18:56 boost_1_59_0
-rw-r--r--  1 root root 83709983 May 11 18:56 boost_1_59_0.tar.gz           =====> //下载好的,解压即可
  • 根据 boost_1_59_0 具体版本号重新定义并 cmake
cmake ...
-DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=/usr/local/boost/boost_1_59_0 \
...
  • 最后的 cmake 编译配置选项
cd build
sudo cmake .. -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
    -DMYSQL_DATADIR=/data/home/coam/data/mysql \
    -DSYSCONFDIR=/etc/mysql \
    -DMYSQL_TCP_PORT=3306 \
    -DMYSQL_UNIX_ADDR=/var/run/mysqld/mysqld.sock \
    -DINSTALL_LIBDIR=/usr/lib/mysql \
    -DINSTALL_PLUGINDIR=/usr/lib/mysql/plugin \
    -DWITH_SYSTEMD=1 \
    -DENABLE_DOWNLOADS=1 \
    -DWITH_EMBEDDED_SERVER=1 \
    -DENABLED_LOCAL_INFILE=1 \
    -DWITH_INNOBASE_STORAGE_ENGINE=1 \
    -DWITH_EXTRA_CHARSETS=complex \
    -DWITH_PARTITION_STORAGE_ENGINE=1 \
    -DWITH_FEDERATED_STORAGE_ENGINE=1 \
    -DWITH_MYISAM_STORAGE_ENGINE=1 \
    -DWITH_ARCHIVE_STORAGE_ENGINE=1 \
    -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
    -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
    -DDOWNLOAD_BOOST=1 \
    -DWITH_BOOST=/usr/local/boost/boost_1_59_0 \
    -DDEFAULT_CHARSET=utf8 \
    -DDEFAULT_COLLATION=utf8_general_ci \
    -DEXTRA_CHARSETS=all \
    -DWITH_SSL=system \
    -DWITH_READLINE=1 \
    -DENABLE_PROFILING=0
  • 执行 make 编译
coam@coam:/opt/data/mysql-5.7.12/build$ sudo make -j

-j 用来指定CPU核心数,可加快编译速度 -j2 -j4 都可能会出现错误 [virtual memory exha.usted: Cannot allocate memory] 不加也可以 漫长的过程 主要看机器的配置,我的虚拟机给了1300M,所以大概用了25分钟

编译过程在云服务器上花费了整整2个小时!!如果这个步骤出现问题,找出问题原因并解决后先 make clean,再重新 make

注:阿里云低配[1Core-1G]服务器编译到[36%]的时候,总是不通过,是因为内存不足,需要分配swap交换内存后并调优

cat /proc/sys/vm/swappiness
// [swappiness:0~100] 临时性的修改 60
sudo sysctl vm.swappiness=60

make install 后会自动创建如下目录文件

  • /usr/local/mysql
  • /usr/lib/mysql
  • /usr/lib/mysql/plugin

第七步,创建 MySQL 的数据目录

cd /data/home/data
mkdir mysql
chown -R mysql.mysql mysql
mkdir /etc/mysql
chown -R mysql.mysql mysql

第八步,拷贝 my.cnf

coam@coam:$cd /usr/local/mysql/support-files
coam@coam:/usr/local/mysql/support-files$ ls
magic  my-default.cnf  mysqld_multi.server  mysql-log-rotate  mysql.server
sudo cp my-default.cnf  /etc/mysql/my.cnf

并编辑

sudo vi /etc/mysql/my.cnf
* [mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
basedir =/usr/local/mysql
datadir =/data/home/data/mysql
port = 3306
# server_id = .....
socket =/var/run/mysqld/mysqld.sock
  • Mysql默认的编码格式是 latin 格式,当然修改成utf8更加方便处理中文
vi /etc/mysql/my.cnf
#在[mysqld]下面加入一行
character_set_server = utf8
#在[mysql]下面加入一行
default-character-set = utf8
/etc/init.d/mysql restart

注:以上配置可直接使用已经在生产环境的 /etc/mysql/my.cnf 替换

  • MySQL初始化安装 - /data/home/data/mysql 需要清空
coam@coam:/usr/local/mysql/support-files$ sudo mkdir /data/home/data/mysql
coam@coam:/usr/local/mysql/support-files$ sudo chown mysql.mysql /data/home/data/mysql
coam@coam:/usr/local/mysql/support-files$ sudo /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/data/home/data/mysql --basedir=/usr/local/mysql --socket=/var/run/mysqld/mysqld.sock
2016-05-18T00:49:36.484135Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-05-18T00:49:36.484233Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2016-05-18T00:49:36.484237Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2016-05-18T00:49:37.099361Z 0 [Warning] InnoDB: New log files created, LSN=45790
2016-05-18T00:49:37.177877Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2016-05-18T00:49:37.239010Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 65571e41-1c92-11e6-813b-5600001238f3.
2016-05-18T00:49:37.240470Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2016-05-18T00:49:37.737636Z 0 [Warning] CA certificate ca.pem is self signed.
2016-05-18T00:49:37.935969Z 1 [Note] A temporary password is generated for root@localhost: N+?GkPFyA9iW
  • 添加 MySQL 服务
coam@coam:/usr/local/mysql/support-files$ sudo cp mysql.server /etc/init.d/mysql
coam@coam:/usr/local/mysql/support-files$ sudo service mysql restart
Shutting down MySQL
.. *
Starting MySQL
. *

/data/home/data/mysql/coam.err

Can't create/write to file '/var/run/mysqld/mysqld.pid
mkdir -p /var/run/mysqld
chown mysql:mysql /var/run/mysqld
* [ERROR] Could not create unix socket lock file /var/run/mysqld/mysqld.sock.lock.
mkdir -p /var/run/mysqld
chown mysql:mysql /var/run/mysqld
  • 刚安装的 Mysql 使用命令行登陆,提示如下错误
coam@coam:~$ mysql -u root -p
MySQL -Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

因为默认 [mysql] 使用 ‘/var/run/mysqld/mysqld.sock’ 但是配置文件仅指定了[mysqld]而没有指定[mysql] 两者 socket 配置应为同一个 mysql.sock 文件 ,于是在 /etc/mysql/my.cnf 添加如下内容重启 Mysql 服务器即可

* [client]

port = 3306
host = 127.0.0.1
socket = /var/run/mysqld/mysqld.sock
  • 出现 The server quit without updating PID file (/data/home/coam/data/mysql/coam.pid) 错误
coam@coam:/usr/local/mysql/support-files$ sudo service mysql restart
 * MySQL server PID file could not be found!
Starting MySQL
. * The server quit without updating PID file (/data/home/coam/data/mysql/coam.pid).
  1. 由于没有进行 sudo /usr/local/mysql/bin/mysqld –initialize… 初始化命令,所以直接启动 mysql 出现此问题 - 清空 dataDir:/data/home/data/mysql 下所有文件

  2. 如果使用其它服务器拷贝的旧的 mysql 数据文件到 /data/home/data/mysql ,则需要使用如下命令修改此目录下的所有用户属主为 mysql

sudo chown mysql.mysql /data/home/data/mysql
sudo chown -R mysql.mysql /data/home/data/mysql/*
  1. /var/log/mysql 日志文件目录不存在

/etc/mysql/my.cnf 中配置了 log_error 错误日志文件 /var/log/mysql 没有自动创建

log_error = /var/log/mysql/error.log

使用 ps -ef 查看默认log_error 目录为 –log-error=/data/home/data/mysql/coam.err

Mon May 16 17:59:13 coam@coam:/var/log$ sudo ps -ef | grep mysql
root     15391     1  0 17:59 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/home/data/mysq  --pid-file=/data/home/data/mysql/coam.pid
mysql    15623 15391  6 17:59 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/home/data/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/data/home/data/mysql/coam.err --pid-file=/data/home/data/mysql/coam.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
coam   15686   581  0 17:59 pts/0    00:00:00 grep --color=auto mysql

解决方案,手动创建 /var/log/mysql 目录 和 /var/log/mysql/error.log 文件;或者注释掉 /etc/mysql/my.cnf 中的 log_error 日志配置

  • 加入开机自启动;并启动 mysql
// 设置. 取消MySQL自动运行
update-rc.d -f mysql defaults                    ======> 设置启动MYSQL
update-rc.d -f mysql remove                      ======> 取消MYSQL启动
//cp support-files/mysql.server /etc/rc.d/init.d/mysql
//chmod 755 /etc/rc.d/init.d/mysql

也可以使用官方提供的 apt 来安装最新版 Mysql

wget http://repo.mysql.com//mysql-apt-config_0.8.0-1_all.deb
dpkg -i mysql-apt-config_xx_all.deb
apt update
apt install mysql-server
  • 卸载
sudo apt remove mysql-server
sudo apt autoremove

通过 deb 包安装

libmysqlclient20_5.7.10-1ubuntu14.04_amd64.deb
libmysqlclient-dev_5.7.10-1ubuntu14.04_amd64.deb
libmysqld-dev_5.7.10-1ubuntu14.04_amd64.deb
mysql-client_5.7.10-1ubuntu14.04_amd64.deb
mysql-common_5.7.10-1ubuntu14.04_amd64.deb
mysql-community-client_5.7.10-1ubuntu14.04_amd64.deb
mysql-community-server_5.7.10-1ubuntu14.04_amd64.deb
mysql-community-source_5.7.10-1ubuntu14.04_amd64.deb
mysql-community-test_5.7.10-1ubuntu14.04_amd64.deb
mysql-server_5.7.10-1ubuntu14.04_amd64.deb
mysql-testsuite_5.7.10-1ubuntu14.04_amd64.deb
mysql-community_5.7.10-1ubuntu14.04_amd64.changes

  • 配置环境变量 为了直接调用 mysql,需要将 mysql 的bin目录加入 PATH 环境变量. 编辑 /etc/profile 文件:
sudo vim /etc/profile

在文件最后 添加如下两行:

PATH=$PATH:/usr/local/mysql/bin
export PATH

关闭文件,运行下面的命令,让配置立即生效:

source /etc/profile

  • 结合 LAMP 一键安装脚本的 my.cnf 文件,只列出其中 [mysqld] 段落中的内容,其他段落内容对 MySQL 运行性能影响甚微,因而姑且忽略.介绍一些优化参数.
* [mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
  • 避免 MySQL 的外部锁定,减少出错几率增强稳定性.
key_buffer_size = 16M
  • 指定用于索引的缓冲区大小,增加它可得到更好的索引处理性能.16M适用于 512MB内存,对于内存在4GB左右的服务器该参数可设置为256M,依此类推即可.注意:该参数值设置的过大反而会是服务器整体效率降低!
max_allowed_packet = 1M
  • MySQL 根据此配置会限制 server 接受的数据包大小.
table_open_cache = 64
  • 指定表高速缓存的大小.每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容. 注意,不能盲目地把table_open_cache设置成很大的值.如果设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失败 64 适用于 512MB 内存,1GB 内存则可以设置成 128,依此类推即可.
sort_buffer_size = 512K
  • 查询排序时所能使用的缓冲区大小.注意:该参数对应的分配内存是每连接独占,如果有100个连接,那么实际分配的总共排序缓冲区大小为100 × 512K = 50MB . 512K 适用于 512MB 内存,1GB 内存则可以设置成 1M,依此类推即可.
net_buffer_length = 8K
  • 初始化server 接受的数据包大小,当需要的时候再由 max_allowed_packet 控制增长的大小.注意:该参数值设置的范围只能为1 – 1024K.
read_buffer_size = 256K
  • 读查询操作所能使用的缓冲区大小.和 sort_buffer_size 一样,该参数对应的分配内存也是每连接独享. 256K 适用于 512MB 内存,1GB 内存则可以设置成 512K,依此类推即可.
read_rnd_buffer_size = 512K
  • 查询操作多表所能使用的缓冲区大小.设置较大的值可以有效提升 ORDER BY 的性能.和 sort_buffer_size 一样,该参数对应的分配内存也是每连接独享.512K适用于 512MB 内存,1GB 内存则可以设置成 1M,依此类推即可.
myisam_sort_buffer_size = 8M
  • MyISAM 排序所能使用的缓冲区大小. 8M 适用于 512MB 内存,1GB 内存则可以设置成 16M,依此类推即可.
max_connections = 256
  • 指定MySQL允许的最大连接进程数.如果在访问时经常出现 Too Many Connections 的错误提示,则需要增大该参数值. 注意:该参数默认值为 151,最大可以设置为 100000 这里建议设置成内存的一半,比如 512MB 内存就设置成 256,依此类推.

  • Mysql的配置文件位置及优先级
coam@coam:/etc/mysql$ /usr/local/mysql/bin/mysql --help | grep my.cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf

查看mysql配置文件的位置,发现mysql读取 my.cnf 的顺序是:

/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

如果这些个文件中,存在相同的参数配置,那就以最右边的文件的参数的值为主.

通过命令行安装的 MySQL,会自动在 /etc/ 下创建mysql文件夹,在最开始如果没有把 /etc/mysql/ 删除的话,/etc/my.cnf 是不起作用的.


注意

清除编译结果

如果编译过程中报错,在执行正确的命令之前,要先执行:

make clean
rm -f CMakeCache.txt

清除编译结果,不然可能会继续错误.


其它

MySQL 5.5版本以后,使用CMake编译工具

configure命令 CMake命令
./configure cmake .
./configure -help -cmake . -LH or ccmake .

“CMAKE_INSTALL_PREFIX”的值表示的是安装根目录,其他参数值的路径都是相对于根目录的,当然你也可以直接使用绝对路径

下面是编译MySQL的新老参数对照表:

参数值说明 配置选项 CMak选项 说明
安装根目录 –prefix=/usr -DCMAKE_INSTALL_PREFIX=/usr
mysqld目录 –libexecdir=/usr/sbin -DINSTALL_SBINDIR=sbin
数据存储目录 –localstatedir=/var/lib/mysql -DMYSQL_DATADIR=/var/lib/mysql 数据库 数据目录
配置文件(my.cnf)目录 –sysconfdir=/etc/mysql -DSYSCONFDIR=/etc/mysql
插件目录 –with-plugindir=/usr/lib64/mysql/plugin -DINSTALL_PLUGINDIR=lib64/mysql/plugin PREFIX/lib/mysql/plugin [指向插件目录]
手册文件目录 –mandir=/usr/share/man -DINSTALL_MANDIR=share/man
共享数据目录 –sharedstatedir=/usr/share/mysql -DINSTALL_SHAREDIR=share
Library库目录 –libdir=/usr/lib64/mysql -DINSTALL_LIBDIR=lib64/mysql PREFIX/lib
Header安装目录 –includedir=/usr/include/mysql -DINSTALL_INCLUDEDIR=include/mysql PREFIX/include
信息文档目录 –infodir=/usr/share/info -DINSTALL_INFODIR=share/info PREFIX/docs
TCP/IP端口 –with-tcp-port-=3306 -DMYSQL_TCP_PORT=3306
UNIX socket文件 –with-unix-socket-path=/tmp/mysqld.sock -DMYSQL_UNIX_ADDR=/tmp/mysqld.sock
启用加载本地数据 –enable-local-infile -DENABLED_LOCAL_INFILE=1 可以使用load data infile命令从本地导入文件 OFF
扩展字符支持 –with-extra-charsets=all -DEXTRA_CHARSETS=all all
默认字符集 –with-charset=utf8 -DDEFAULT_CHARSET=utf8 latin1
默认字符校对编码排序规则 –with-collation=utf8_general_ci -DDEFAULT_COLLATION=utf8_general_ci latin1_swedish_ci [utf8_general_ci快速/utf8_unicode_ci准确]
Build the server –with-server
编译嵌入式服务器支持 –with-embedded-server -DWITH_EMBEDDED_SERVER=1 OFF
libmysqld权限控制 –with-embedded-privilege-control
安装文档 –without-docs
Big tables支持 –with-big-tables, –without-big-tables
mysqld运行用户 –with-mysqld-user=mysql -DMYSQL_USER=mysql mysql
调试模式 –without-debug -DWITH_DEBUG=0 OFF
GIS支持 –with-geometry
社区功能 –enable-community-features
禁用Profiling分析 –disable-profiling -DENABLE_PROFILING=0 启用
汇编字符串函数 –enable-assembler
构建类型 –build=x86_64-pc-linux-gnu 没有等效参数
交叉编译主机 –host=x86_64-pc-linux-gnu 没有等效参数
客户端标志 –with-client-ldflags=-lstdc++
线程安全标志 –enable-thread-safe-client
注释存储类型 –with-comment=‘string’ -DWITH_COMMENT=‘string’
Shared/static binaries –enable-shared –enable-static
内存使用控制 –with-low-memory
额外的字符集 -DWITH_EXTRA_CHARSETS ALL
静态编译xxx 存储引擎 -WITH_xxx_STORAGE_ENGINE 默认的存储引擎,不加也可以
编译myisam存储引擎 -DWITH_MYISAM_STORAGE_ENGINE 默认的存储引擎,不加也可以
支持InnoDB存储引擎 -DWITH_INNOBASE_STORAGE_ENGINE 支持InnoDB存储引擎,这个也是默认安装的
使用readline功能 -DWITH_READLINE OFF [提供可编辑的命令行]
是否启用同步调试功能 -ENABLE_DEBUG_SYNC ON
是否下载可选文件 -ENABLE_DOWNLOADS OFF
是否启用代码查询分析 -ENABLED_PROFILING ON
MySQL 主执行文件目录 -INSTALL_BINDIR PREFIX/bin
文档安装路径 -INSTALL_DOCDIR PREFIX/docs
自述文件目录 -INSTALL_DOCREADMEDIR PREFIX
共享数据目录 -INSTALL_MYSQLSHAREDIR PREFIX/share
mysql-test 目录 -INSTALL_MYSQLTESTDIR PREFIX/mysql-test
脚本目录 -INSTALL_SCRIPTDIR PREFIX/scripts
sql-bench 性能测试工具目录 -INSTALL_SQLBENCHDIR PREFIX
扩展支持文件目录 -INSTALL_SUPPORTFILESDIR PREFIX/support-files
是否启用MySQL的维护环境 -MYSQL_MAINTAINER_MODE OFF
是否包含 DTrace 支持 -ENABLE_DTRACE PREFIX
是否包含 Gcov 支持 -ENABLE_GCOV PREFIX
是否建立单个安装包文件 -CPACK_MONOLITHIC_INSTALL OFF
是否支持SSL -DWITH_SSL system [安全套接层]
是否支持Zlib -DWITH_ZLIB system [启用libz库支持(zib. gzib相关)]
禁用libwrap库 –without-libwrap -DWITH_LIBWRAP=0 实现了通用TCP包装的功能,为网络服务守护进程使用

存储引擎选项

存储引擎是以插件的形式存在的,所以,该选项可以控制插件的构建,比如指定使用某个特定的引擎. –with-plugins 配置选项接受两种形式的参数值,它没有对应的 CMake 配置参数:

在CMake中,引擎被作为单个的选项来进行控制.假设有以下配置选项:

--with-plugins=csv,myisam,myisammrg,heap,innobase,archive,blackhole

上面的参数指定MySQL数据库可以支持哪些数据库引擎,将上述编译选项转换成CMake编译选项时,下面的几个引擎名字可以被省略,因为编译时,默认就支持:

csv myisam myisammrg heap 

然后使用下面的编译参数,以启用InnoDB. ARCHIVE和BLACKHOLE引擎支持:

-DWITH_INNOBASE_STORAGE_ENGINE=1
-DWITH_ARCHIVE_STORAGE_ENGINE=1
-DWITH_BLACKHOLE_STORAGE_ENGINE=1

当然也可以使用”ON”来替代数字1,它们是等效的. 如果你想除去对某种引擎的支持,则在CMake编译选项中使用 -DWITHOUT__STORAGE_ENGINE,例如:

-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1
-DWITHOUT_FEDERATED_STORAGE_ENGINE=1
-DWITHOUT_PARTITION_STORAGE_ENGINE=1

  • 库文件加载选项

该选项指明 Mysql 使用库的情况:

参数值说明 配置选项 CMak选项
readline库 –with-readline -DWITH_READLINE=1
SSL库 –with-ssl=/usr -DWITH_SSL=system
zlib库 –with-zlib-dir=/usr -DWITH_ZLIB=system
libwrap库 –without-libwrap -DWITH_LIBWRAP=0
  • 其他选项

CMake编译选项支持大部分之前版本的 MySQL 编译选项,新老编译选项的差别在于: * 之前的是小写,现在全部变成了大写,之前采用双横线,现在使用单横线,之前使用的破折号,现在取而代之的是使用下划线, 例如:

--with-debug => WITH_DEBUG=1 
--with-embedded-server => WITH_EMBEDDED_SERVER

通过 apt 形式安装 mysql扩展

sudo apt install php5-mysql

以上命令将包含创建 mysql.so 和 mysqli.so 两个扩展


安装 Mysql C++ connector 1.1.7

//git clone https://github.com/mysql/mysql-connector-cpp.git
wget https://github.com/mysql/mysql-connector-cpp/archive/1.1.7.zip
cd mysql-connector-cpp
mkdir build
cd build
sudo cmake ..
sudo make
sudo make install

最后会安装到如下目录:

-- Installing: /usr/local/include/cppconn/build_config.h
-- Installing: /usr/local/include/cppconn/config.h
-- Installing: /usr/local/include/cppconn/connection.h
-- Installing: /usr/local/include/cppconn/datatype.h
-- Installing: /usr/local/include/cppconn/driver.h
-- Installing: /usr/local/include/cppconn/exception.h
-- Installing: /usr/local/include/cppconn/metadata.h
-- Installing: /usr/local/include/cppconn/parameter_metadata.h
-- Installing: /usr/local/include/cppconn/prepared_statement.h
-- Installing: /usr/local/include/cppconn/resultset.h
-- Installing: /usr/local/include/cppconn/resultset_metadata.h
-- Installing: /usr/local/include/cppconn/statement.h
-- Installing: /usr/local/include/cppconn/sqlstring.h
-- Installing: /usr/local/include/cppconn/warning.h
-- Installing: /usr/local/include/cppconn/version_info.h
-- Installing: /usr/local/include/cppconn/variant.h

-- Installing: /usr/local/lib/libmysqlcppconn.so.7.1.1.7
-- Installing: /usr/local/lib/libmysqlcppconn.so.7
-- Installing: /usr/local/lib/libmysqlcppconn.so
-- Installing: /usr/local/lib/libmysqlcppconn-static.a

-- Installing: /usr/local/include/mysql_connection.h
-- Installing: /usr/local/include/mysql_driver.h
-- Installing: /usr/local/include/mysql_error.h
  • 注意, cmake 时要添加 sudo 选项,否则 提示如下 找不到 boost 库错误
-- Could NOT find Boost
CMake Error at CMakeLists.txt:160 (MESSAGE):
  Boost or some of its libraries found.  If not in standard place please set
  -DBOOST_ROOT:STRING=

*

g++ -o framework -I/usr/local/include -I/usr/local/include/cppconn -lmysqlcppconn framework.cpp
/usr/local/include/mysql_connection.h:31:32: fatal error: boost/shared_ptr.hpp: No such file or directory
  • cmake 的时候,添加 -DBOOST_ROOT:STRING= 参数
sudo cmake -DBOOST_ROOT:STRING=/usr/local/boost/boost_1_59_0 ..

注意:如果编译失败,我讲以上文件复制到目标机器也可以


暂未使用 # mysql-connector-cpp 2.0+


wget https://github.com/mysql/mysql-connector-cpp/archive/2.0.3.tar.gz

Install the project...
-- Install configuration: ""
-- Installing: /usr/local/mysql/connector-c++-2.0/include/mysql_common.h
-- Installing: /usr/local/mysql/connector-c++-2.0/include/mysql_xapi.h
-- Installing: /usr/local/mysql/connector-c++-2.0/include/mysql_devapi.h
-- Installing: /usr/local/mysql/connector-c++-2.0/include/devapi/common.h
-- Installing: /usr/local/mysql/connector-c++-2.0/include/devapi/result.h
-- Installing: /usr/local/mysql/connector-c++-2.0/include/devapi/statement.h
-- Installing: /usr/local/mysql/connector-c++-2.0/include/devapi/document.h
-- Installing: /usr/local/mysql/connector-c++-2.0/include/devapi/crud.h
-- Installing: /usr/local/mysql/connector-c++-2.0/include/devapi/collection_crud.h
-- Installing: /usr/local/mysql/connector-c++-2.0/include/devapi/table_crud.h
-- Installing: /usr/local/mysql/connector-c++-2.0/include/devapi/collations.h

MySQL重设 root 密码

  • 在 make install 最后一行有显示,也可以之后根据 mysql 日志搜索得出
$sudo grep 'temporary password' /var/log/mysql.log
  • MySQL5.7在安装完后,第一次启动时,会在 root 目录下生产一个随机的密码,文件名为 .mysql_secret —貌似没有

登录时需要用随机密码登录,然后通过以下命令修改密码

SET PASSWORD = PASSWORD('******');

  • MySQL下创建新用户. 新数据库. 设定访问权限控制都需要用到root密码.万一把root密码忘了,该怎么办?
sudo service mysql stop

以上命令适用于 Ubuntu 和 Debian.CentOS. Fedora 和 RHEL 下使用 mysqld 替换 mysql.

以安全模式启动 mysql:

cd /usr/local/mysql/bin/
sudo ./mysqld_safe --skip-grant-tables --skip-networking &

注意我们加了 –skip-networking ,避免远程无密码登录 MySQL.(感谢 RobberPhex指出.)

这样我们就可以直接用root登录,无需密码:

mysql -u root

mysql5.7.*

use mysql;
UPDATE mysql.user SET authentication_string = PASSWORD('******') WHERE User = 'root' AND Host = 'localhost';
flush privileges;
mysql > quit
  • mysql5.7 密码 Expiration 策略
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE;
  • 也可以通过如下命令取消密码 Expiration 策略
ALTER USER 'script'@'localhost' PASSWORD EXPIRE NEVER
  • 刚重设 mysql root 密码后,phpmyadmin 不能使用重设的 root 密码登陆,而在命令行模式下可以使用 root 账户密码登陆但是执行任何操作都会出现如下错误:
mysql> use mysql;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
SET PASSWORD = PASSWORD('new password');

安装 Mysql-Shell

Wed May 31 05:35:08 coam@ms:~/DevC/my_plus/build$ mysqlsh -u zhangyanxi --sqlc -e "show plugins"
mysqlsh: command not found

/opt/data

wget https://cdn.mysql.com//Downloads/MySQL-Shell/mysql-shell_1.0.9-1ubuntu16.04_amd64.deb
sudo dpkg -i mysql-shell_1.0.9-1ubuntu16.04_amd64.deb
  • 如果提示依赖 libprotobuf9v5
sudo apt install libprotobuf9v5

安装完 Mysql-Shell 插件后,/usr/lib/mysql/plugin/ 目录下会新增 mysqlx.so

启用 mysqlx

mysqlsh -u zhangyanxi -h localhost --classic --dba enableXProtocol

或者

$ mysql -u zhangyanxi -p
mysql> INSTALL PLUGIN mysqlx SONAME 'mysqlx.so';

查看 mysqlx 是否启用

mysqlsh -u zhangyanxi --sqlc -e "show plugins"
+----------------------------+----------+--------------------+-----------+---------+
| Name                       | Status   | Type               | Library   | License |
+----------------------------+----------+--------------------+-----------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | null      | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | null      | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | null      | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | null      | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | null      | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | null      | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_TEMP_TABLE_INFO     | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| INNODB_SYS_VIRTUAL         | ACTIVE   | INFORMATION SCHEMA | null      | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | null      | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | null      | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | null      | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | null      | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | null      | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | null      | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | null      | GPL     |
| ngram                      | ACTIVE   | FTPARSER           | null      | GPL     |
| mysqlx                     | ACTIVE   | DAEMON             | mysqlx.so | GPL     |
+----------------------------+----------+--------------------+-----------+---------+

可以看到已经安装…

参考: * Setting Up MySQL as a Document Store


  • 源码编译安装 Mysql 5.7.18 后启动 Mysql 后,[coam.err]提示如下错误:
* [ERROR] /usr/local/mysql/bin/mysqld: Can't create/write to file '/var/run/mysqld/mysqld.pid' (Errcode: 2 - No such file or directory)

可以通过如下命令解决:

sudo mkdir /var/run/mysqld
sudo chown -R mysql.mysql /var/run/mysqld
sudo chmod -R 777 /var/run/mysqld

但由于 mysql 用户没有直接创建 /var/run/mysqld 的权限,而 /var/run/* 下的文件在服务器重启后会删除 可以考虑将所有 mysql.pie mysql.sock 配置为一特定目录 /tmp/mysqld/ 下,并分配 Mysql 用户的权限…

  • 由于源码编译安装默认的 /usr/local/mysql/lib/systemd/system/cs-mysqld.service 文件配置的为
PIDFile=/var/run/mysqld/mysqld.pid

最后利用 tmpfiles.d 在系统重启时添加临时文件夹 /var/run/mysqld/

/etc/tmpfiles.d/mysql.conf

# systemd tmpfile settings for mysql
# See tmpfiles.d(5) for details

d /var/run/mysqld 0755 mysql mysql -

重启后,可以看到 /var/run/mysqld 已经自动创建…

2017-06-02T09:55:08.123081Z 0 [ERROR] Incorrect definition of table performance_schema.replication_connection_status: expected column 'RECEIVED_TRANSACTION_SET' at position 7 to have type longtext, found type text.
2017-06-02T09:55:08.123421Z 0 [ERROR] Incorrect definition of table performance_schema.replication_group_member_stats: expected column 'COUNT_TRANSACTIONS_ROWS_VALIDATING' at position 6, found 'COUNT_TRANSACTIONS_VALIDATING'.
  • 错误的原因是升级到5.7.18后,有一些表的结构改变了,执行mysql_upgrade命令即可修复.
mysql_upgrade -u zhangyanxi -p
  • 如果提示如下错误:

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

sudo chmod -R 777 /data/home/data/mysql

参考列表

Mysql5.7 Json

Comments

Cor-Ethan, the beverage → www.iirii.com