怎样编译安装MySQL

2016年10月28日 | 分类: 【技术】

【依赖】

安装CMake：

参考：https://amon.org/cmake

安装Bison：

参考：https://amon.org/bison

安装Boost库：

参考：https://amon.org/boost

安装ncurses库：

apt-get install libncurses5-dev

【安装】

下载源码编译安装MySQL：

下载：https://dev.mysql.com/downloads/mysql/5.7.html#downloads

最新：http://dev.mysql.com/downloads/mysql/

wget -c http://cdn.mysql.com/Downloads/MySQL-5.7/mysql-5.7.31.tar.gz && tar -zxvf mysql-5.7.31.tar.gz && cd mysql-5.7.31
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DSYSCONFDIR=/etc -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
nohup make -j4 &
make install

编译安装完成。

创建mysql用户及用户组：

查看是否有mysql用户及用户组：

cat /etc/passwd # 查看用户列表
cat /etc/group  # 查看用户组列表

如果没有，就创建：

groupadd mysql && useradd -g mysql mysql

修改 /usr/local/mysql 权限：

chown -R mysql:mysql /usr/local/mysql

将MySQL编译生成的bin目录添加到当前Linux系统的环境变量中：

echo -e '\n\nexport PATH=/usr/local/mysql/bin:$PATH\n' >> /etc/profile && source /etc/profile

初始化MySQL自身的数据库：

在MySQL安装目录的\bin\路径下，执行mysqld命令，初始化MySQL自身的数据库：

cd /usr/local/mysql/bin/ && mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --explicit_defaults_for_timestamp

查看是否生成了MySQL自身的数据库文件：

ls -lrt /usr/local/mysql/data/

配置开机自启动：

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
chkconfig --add mysqld 
chkconfig mysqld on

创建MySQL日志存放目录：

下面配置的MySQL日志存放目录以及权限都是根据前面my.cnf文件写的，也就是两者需要保持一致。

mkdir -p /var/log/mysql
chown -R mysql:mysql /var/log/mysql

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

编辑 /etc/my.cnf ：

下面配置的MySQL日志存放目录以及权限与前面my.cnf文件保持一致：


[client]
port=3306
socket=/var/lib/mysql/mysql.sock

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port=3306
server-id = 1
socket=/var/lib/mysql/mysql.sock

character-set-server = utf8
log-error = /var/log/mysql/error.log
pid-file = /var/log/mysql/mysql.pid
general_log = 1
back_log = 300

max_connections = 1000
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 128 
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M

read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 28M
key_buffer_size = 4M

thread_cache_size = 8

query_cache_type = 1
query_cache_size = 8M
query_cache_limit = 2M

ft_min_word_len = 4

log_bin = mysql-bin
binlog_format = mixed
expire_logs_days = 30

performance_schema = 0
explicit_defaults_for_timestamp

myisam_sort_buffer_size = 8M
myisam_repair_threads = 1

interactive_timeout = 28800
wait_timeout = 28800

symbolic-links=0

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

启动MySQL服务：

启动MySQL进程服务：

mysqld_safe --user=mysql --datadir=/usr/local/mysql/data --log-error=/var/log/mysql/error.log &

输出：

[1] 10274
150513 21:28:16 mysqld_safe Logging to '/var/log/mysql/error.log'.
150513 21:28:16 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

直接回车，这条命令会在后台继续执行。

启动MySQL：

service mysqld start

如果启动成功，输出：

Starting MySQL SUCCESS!

如果启动失败，输出：

Starting MySQL... ERROR! The server quit without updating PID file (/var/log/mysql/mysql.pid).

可见是.pid文件的配置有出入，务必保证编译配置时的路径和 my.cnf 中的路径保持一致。因为MySQL默认的my.conf。

现在打开 /usr/local/mysql/data/ ，删除刚才生成的一些文件，重新执行mysqld命令，初始化MySQL自身的数据库。

查看MySQL服务进程：

ps -ef | grep mysql

查看MySQL端口监听情况：

netstat -tunpl | grep 3306

初始化MySQL数据库的root用户密码：

MySQL安全配置向导mysql_secure_installation各项配置能够提高mysql库的安全。
MySQL数据库默认自带了一个root用户，在设置好MySQL数据库的安全配置后初始化root用户的密码。
MySQL新增了密码验证插件（VALIDATE PASSWORD PLUGIN），用户密码策略分成低级LOW、中等MEDIUM和超强STRONG三种。如果考虑启用这个插件，推荐使用中等MEDIUM级别。

配置过程中，可以一路输入y。建议仔细阅读操作过程中显示的指南。

mysql_secure_installation

输出：


Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD PLUGIN can be used to test passwords [是否使用密码验证插件，直接回车，选择不使用]
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No:

Please set the password for root here. [初始化root用户的密码，输入两次新密码]

New password:

Re-enter new password:

By default, a MySQL installation has an anonymous user, [删除匿名用户，输入 y]
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from [root用户仅能本地访问，不允许远程访问，输入 y]
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that [删除test数据库库和对test数据库的访问权限，输入 y]
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No)  : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes [重载用户权限表，输入 y]
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

将MySQL数据库的动态链接库共享至系统链接库：

MySQL数据库会被PHP等服务调用，所以需要将MySQL编译后的lib库文件添加至当前Linux主机链接库/etc/ld.so.conf.d/下，这样MySQL服务就可以被其它服务调用了。

echo "/usr/local/mysql/lib" > /etc/ld.so.conf.d/mysql.conf
ldconfig

ldconfig -v |grep mysql

输出：

/usr/lib64/mysql:
        libmysqlclient.so.18 -> libmysqlclient.so.18.0.0
/usr/local/mysql/lib:
        libmysqlclient.so.20 -> libmysqlclient_r.so.20.0.0

查看MySQL版本：

mysql -v -p
Enter password:

输入MySQL密码，输出：

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.29-log Source distribution

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.

Reading history-file /root/.mysql_history
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

【其他】

MySQL日志管理：

MySQL的日志将会占用很大空间，因此需要一定的管理策略。

mysql-bin.000002这样文件存放的是数据库每天增加的数据，所有数据库的数据增量都在这里面，如何删除mysql-bin.0000X 日志文件呢?

mysql -u root -p
mysql> reset master;

参考：《解决”mysql-bin.000001″占用超大空间的问题》
参考：《mysql 利用binlog增量备份，还原实例》
参考：《MySQL 删除binlog日志文件》
参考：《mysql-bin引起mysql不能启动》
参考：http://blog.csdn.net/alishun/article/details/5084318

取消 mysql 开机自启动：

参考：https://blog.csdn.net/maxracer/article/details/53467308
参考：http://www.voidcn.com/article/p-tzopovyx-go.html

chkconfig --list
chkconfig --level 2345 mysqld off
reboot

【报错】

报错：MySQL占用空间和磁盘空间对应

删除一个MySQL占用空间2.3G的数据库，释放磁盘空间2.3G。

磁盘空间不足时，报错：

Binary logging not possible. Message: An error occurred during flush stage of the commit. 'binlog_error_action' is set to 'ABORT_SERVER'. Hence aborting the server.

报错：mysqli_real_connect(): (HY000/2002): No such file or directory

升级了LAMP，发现不能通过phpmyadmin访问mysql，网页显示：

 #2002 - No such file or directory — 服务器没有响应（或本地服务器的套接字没有正确设置）。
mysqli_real_connect(): (HY000/2002): No such file or directory

参考：http://www.codes51.com/article/detail_1996783.html

这个错误是由于无法找到 mysql.sock 文件

启动mysql服务并查看mysql的socket路径，可以通过mysql命令行 STATUS;

 mysql -v -p
Enter password:
...
mysql> STATUS;

输出：

...
UNIX socket:            /tmp/mysql.sock
...

打开php.ini文件中，找到负责数据库连接三种类型的参数：

pdo_mysql.default_socket=
mysqli.default_socket =
mysql.default_socket=

设置三个参数的值是否和mysql的socket值一致。

然后重启httpd，打开phpmyadmin访问mysql正常。

报错：MySQL端口被占用败

升级MySQL后，测试启动失败。查看MySQL日志：/var/log/mysql/mysqld.log ，发现：

2017-01-09T14:23:52.997666Z 0 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use
2017-01-09T14:23:52.997669Z 0 [ERROR] Do you already have another mysqld server running on port: 3306 ?
2017-01-09T14:23:52.997676Z 0 [ERROR] Aborting

看出 mysqld 3306 端口被占用。

查看什么服务占用3306端口：

netstat -apn  | grep 3306

输出：

tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      4025/mysqld

杀死该进程：

kill -9 4025

再启动，成功！

标签: