centon6上编译mysql5.7_Centos6.5 源码编译安装 Mysql5.7.11及配置

安装环境

Linux(CentOS6.5 版)、boost_1_59_0.tar.gz、mysql-5.7.11.tar.gz

MySQL 5.7主要特性：

更好的性能：对于多核CPU、固态硬盘、锁有着更好的优化，每秒100W QPS已不再是MySQL的追求，下个版本能否上200W QPS才是吾等用户更关心的

更好的InnoDB存储引擎

更为健壮的复制功能：复制带来了数据完全不丢失的方案，传统金融客户也可以选择使用MySQL数据库。此外，GTID在线平滑升级也变得可能

更好的优化器：优化器代码重构的意义将在这个版本及以后的版本中带来巨大的改进，Oracle官方正在解决MySQL之前最大的难题

原生JSON类型的支持

更好的地理信息服务支持：InnoDB原生支持地理位置类型，支持GeoJSON，GeoHash特性

新增sys库：以后这会是DBA访问最频繁的库

步骤一

创建mysql用户组及用户：

groupadd mysql

useradd -r -g mysql mysql

创建数据库相关目录：

mkdir -p /opt/usr/mysql/5.7.11

mkdir -p /opt/usr/mysql/5.7.11/mysqldata

chown -R mysql:mysql /opt/usr/mysql/5.7.11/

步骤二

解压

tar xzf boost_1_59_0.tar.gz

tar xzf mysql-5.7.11.tar.gz

预编译:

cmake \-DCMAKE_INSTALL_PREFIX=/opt/usr/mysql/5.7.11

-DMYSQL_DATADIR=/opt/usr/mysql/5.7.17/mysqldata

-DSYSCONFDIR=/etc

-DWITH_MYISAM_STORAGE_ENGINE=1

-DWITH_INNOBASE_STORAGE_ENGINE=1

-DWITH_MEMORY_STORAGE_ENGINE=1

-DWITH_READLINE=1

-DWITH_ARCHIVE_STORAGE_ENGINE=1

-DWITH_PARTITION_STORAGE_ENGINE=1

-DWITH_BLACKHOLE_STORAGE_ENGINE=1

-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock

-DMYSQL_TCP_PORT=3306

-DENABLED_LOCAL_INFILE=1

-DEXTRA_CHARSETS=all

-DDEFAULT_CHARSET=utf8

-DDEFAULT_COLLATION=utf8_general_ci

-DWITH_EMBEDDED_SERVER=1

-DWITH_LIBWRAP=0

-DWITH_BOOST=/tools/boost_1_59_0 (解压路径)

编译安装

marke

marke install

注意：如果需要重装mysql，在/home/software/mysql-5.7.11再执行下make install就可以了，不需要再cmake和make

修改目录拥有者

chown -R mysql:mysql /opt/usr/mysql/5.7.11/

提示错误，需要安装bison*

CMake Warning at cmake/bison.cmake:20 (MESSAGE):

Bison executable not found in PATH

Call Stack (most recent call first):

sql/CMakeLists.txt:514 (INCLUDE)

CMake Warning at cmake/bison.cmake:20 (MESSAGE):

Bison executable not found in PATH

Call Stack (most recent call first):

libmysqld/CMakeLists.txt:142 (INCLUDE)

yum -y install bison*

[root@tbwy02 mysql-5.7.11]# make

[root@tbwy02 mysql-5.7.11]# make install

[root@tbwy02 5.7.11]# pwd

/opt/usr/mysql/5.7.11

5.7 以后 初始化数据

[root@tbwy02 5.7.11]# /opt/usr/mysql/5.7.11/bin/mysqld --initialize --basedir=/opt/usr/mysql/5.7.11/ --datadir=/opt/usr/mysql/5.7.11/mysqldata/ --user=mysql

[root@tbwy02 5.7.11]# pwd

/opt/usr/mysql/5.7.11

[root@tbwy02 5.7.11]# bin/mysqld --initialize --basedir=/opt/usr/mysql/5.7.11/ --datadir=/opt/usr/mysql/5.7.11/mysqldata/ --user=mysql

[root@tbwy02 5.7.11]# cp support-files/mysql.server /etc/init.d/mysqld

[root@tbwy02 5.7.11]# chmod +x /etc/init.d/mysqld

[root@tbwy02 5.7.11]# chkconfig --add mysqld

[root@tbwy02 5.7.11]# service mysqld start

Starting MySQL.                                            [  OK  ]

[root@tbwy02 5.7.11]# ps -ef |grep mysql

root     29916     1  0 21:55 pts/1    00:00:00 /bin/sh /opt/usr/mysql/5.7.11/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/tbwy02.pid

mysql    30088 29916  2 21:55 pts/1    00:00:00 /opt/usr/mysql/5.7.11/bin/mysqld --basedir=/opt/usr/mysql/5.7.11 --datadir=/var/lib/mysql --plugin-dir=/opt/usr/mysql/5.7.11/lib/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/lib/mysql/tbwy02.pid --socket=/var/lib/mysql/mysql.sock

root     30121 12930  0 21:55 pts/1    00:00:00 grep mysql

2017-6-29 10:36

[root@tbwy02 5.7.11]# pwd

/opt/usr/mysql/5.7.11

会在mysql目录内生成一个data目录，存放数据库的目录

[root@tbwy02 5.7.11]# bin/mysqld --initialize --basedir=/opt/usr/mysql/5.7.11/ --datadir=/opt/usr/mysql/5.7.11/mysqldata/ --user=mysql

返回结果最后一行的末尾有随机密码，我的记下来：al,q16OSkeh

2017-06-29T02:14:20.904715Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2017-06-29T02:14:21.614429Z 0 [Warning] InnoDB: New log files created, LSN=45790

2017-06-29T02:14:21.820184Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2017-06-29T02:14:21.925884Z 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: aa2d031f-5c70-11e7-a3f2-b8ca3af70415.

2017-06-29T02:14:21.941971Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2017-06-29T02:14:21.943214Z 1 [Note] A temporary password is generated for root@localhost: =al,q16OSkeh

想设置默认密码为空则将--initialize选项替换为--initialize-insecure选项

bin/mysqld --initialize-insecure --basedir=/opt/usr/mysql/5.7.11/ --datadir=/opt/usr/mysql/5.7.11/mysqldata/ --user=mysql

[root@tbwy02 5.7.11]# cat  /etc/my.cnf

[mysqld]

#datadir=/var/lib/mysql

#socket=/var/lib/mysql/mysql.sock

# zkm 2017-6-29

datadir=/opt/usr/mysql/5.7.11/mysqldata/

socket=/usr/local/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

[root@tbwy02 5.7.11]#

[root@tbwy02 5.7.11]# service mysqld stop

[root@tbwy02 5.7.11]# service mysqld restart

[root@tbwy02 5.7.11]# cd /opt/usr/mysql/5.7.11/mysqldata

[root@tbwy02 mysqldata]# rm -rf *

[root@tbwy02 5.7.11]# bin/mysqld --initialize-insecure --basedir=/opt/usr/mysql/5.7.11/ --datadir=/opt/usr/mysql/5.7.11/mysqldata/ --user=mysql

2017-06-29T02:32:26.314704Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2017-06-29T02:32:27.008945Z 0 [Warning] InnoDB: New log files created, LSN=45790

2017-06-29T02:32:27.209595Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2017-06-29T02:32:27.305079Z 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: 311cbbe4-5c73-11e7-b8f1-b8ca3af70415.

2017-06-29T02:32:27.322605Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2017-06-29T02:32:27.323494Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

[root@tbwy02 5.7.11]#

配置环境变量

vim /etc/profile

# zkm 2017-6-29 11:31

PATH=/opt/usr/mysql/5.7.11/bin:$PATH

export PATH

source /etc/profile

[root@tbwy02 5.7.11]# mysql -uroot -p

默认密码为空

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| cblsqldb           |

| mysql              |

| performance_schema |

| test               |

+--------------------+

5 rows in set (0.00 sec)

mysql> use mysql;

Database changed

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> drop user 'zkm'@'%';

ERROR 1396 (HY000): Operation DROP USER failed for 'zkm'@'%'

解决方法：

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

创建用户：

mysql> create user 'tbwytest'@'%' identified by 'tbwytest123';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'tbwytest'@'%' IDENTIFIED BY 'tbwytest123' WITH GRANT OPTION;

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password from mysql.user;

ERROR 1054 (42S22): Unknown column 'password' in 'field list'

mysql> select user,host from mysql.user;

+-----------+-----------+

| user      | host      |

+-----------+-----------+

| tbwytest  | %         |

| mysql.sys | localhost |

| root      | localhost |

+-----------+-----------+

3 rows in set (0.00 sec)

mysql>