MySQL8.0.11正式版编译安装

发表评论
693

A+

所属分类：Database

MySQL8.0正式版8.0.11已发布,官方表示MySQL8要比MySQL5.7快2倍,还带来了大量的改进和更快的性能！

注意：从 MySQL 5.7 升级到 MySQL 8.0 仅支持通过使用 in-place 方式进行升级，并且不支持从 MySQL 8.0 降级到 MySQL 5.7（或从某个 MySQL 8.0 版本降级到任意一个更早的 MySQL 8.0 版本）。唯一受支持的替代方案是在升级之前对数据进行备份。

系统：

[root@DB1 ~]# echo $HOSTNAME
DB1.t4x.org
[root@DB1 ~]# uname -a
Linux DB1.t4x.org 3.10.0-327.el7.x86_64 #1 SMP Thu Nov 19 22:10:57 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux
[root@DB1 ~]# cat /etc/redhat-release 
CentOS Linux release 7.2.1511 (Core)

[root@DB1 ~]# echo $HOSTNAME

DB1.t4x.org

[root@DB1 ~]# uname -a

Linux DB1.t4x.org 3.10.0-327.el7.x86_64 #1 SMP Thu Nov 19 22:10:57 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

[root@DB1 ~]# cat /etc/redhat-release

CentOS Linux release 7.2.1511 (Core)

编译：

$ yum install vim wget cmake git gcc openssl openssl-devel gcc-c++ numactl numactl-devel ncurses ncurses-devel -y
$ wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-boost-8.0.11.tar.gz
$ tar zxf mysql-boost-8.0.11.tar.gz
$ groupadd -r mysql -g 27
$ useradd -r mysql -u 27 -g mysql -d /data/ -s /sbin/nologin
$ cmake .. -LAH    #编译参数
$ cmake . -DCMAKE_INSTALL_PREFIX=/opt/mysql-8.0.11 -DMYSQL_UNIX_ADDR=/opt/mysql-8.0.11/tmp/mysql.sock -DMYSQL_DATADIR=/data/3306/data -DWITH_DEBUG=0 -DEXTRA_CHARSETS=all -DWITH_READLINE=1 -DWITH_ZLIB=system -DWITH_SSL=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_USER=mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci  -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DENABLE_DOWNLOADS=1 -DDOWNLOAD_BOOST=1 -DWITH_BOOST=./boost/
$ make && make install
$ ln -s /opt/mysql-8.0.11/ /usr/local/mysql
$ mkdir /usr/local/mysql/tmp
$ chown -R mysql.mysql /usr/local/mysql/tmp/
$ mkdir /data/3306/data -p
$ chown -R mysql.mysql /data/3306/
$ cp support-files/mysql.server /etc/init.d/mysqld
$ /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --datadir=/data/3306/data
$ chmod +x /etc/init.d/mysqld 
$ mv /etc/my.cnf /etc/my.cnf.$(date +%F)
$ cat /etc/my.cnf
[client]
port    = 3306
socket  = /usr/local/mysql/tmp/mysql.sock
[mysqld]
user    = mysql
port    = 3306
socket  = /usr/local/mysql/tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/3306/data
$ /etc/init.d/mysqld start
> CREATE USER 'root'@'%' IDENTIFIED BY 'admin';
> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'admin';

$ yum install vim wget cmake git gcc openssl openssl-devel gcc-c++ numactl numactl-devel ncurses ncurses-devel -y

$ wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-boost-8.0.11.tar.gz

$ tar zxf mysql-boost-8.0.11.tar.gz

$ groupadd -r mysql -g 27

$ useradd -r mysql -u 27 -g mysql -d /data/ -s /sbin/nologin

$ cmake .. -LAH #编译参数

$ cmake . -DCMAKE_INSTALL_PREFIX=/opt/mysql-8.0.11 -DMYSQL_UNIX_ADDR=/opt/mysql-8.0.11/tmp/mysql.sock -DMYSQL_DATADIR=/data/3306/data -DWITH_DEBUG=0 -DEXTRA_CHARSETS=all -DWITH_READLINE=1 -DWITH_ZLIB=system -DWITH_SSL=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_USER=mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DENABLE_DOWNLOADS=1 -DDOWNLOAD_BOOST=1 -DWITH_BOOST=./boost/

$ make && make install

$ ln -s /opt/mysql-8.0.11/ /usr/local/mysql

$ mkdir /usr/local/mysql/tmp

$ chown -R mysql.mysql /usr/local/mysql/tmp/

$ mkdir /data/3306/data -p

$ chown -R mysql.mysql /data/3306/

$ cp support-files/mysql.server /etc/init.d/mysqld

$ /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --datadir=/data/3306/data

$ chmod +x /etc/init.d/mysqld

$ mv /etc/my.cnf /etc/my.cnf.$(date +%F)

$ cat /etc/my.cnf

[client]

port = 3306

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

[mysqld]

user = mysql

port = 3306

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

basedir = /usr/local/mysql

datadir = /data/3306/data

$ /etc/init.d/mysqld start

> CREATE USER 'root'@'%' IDENTIFIED BY 'admin';

> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'admin';

基准测试：1核1G内存 [VM虚拟机测试,不代表实际效果]

MySQL5.7.18 测试负载7.41

$ sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=1.1.1.144 --mysql-port=3306 --mysql-user=root --mysql-password=admin --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=120 --report-interval=10 prepare $ sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=1.1.1.144 --mysql-port=3306 --mysql-user=root --mysql-password=admin --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=120 --report-interval=10 run

0
1

$ sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=1.1.1.144 --mysql-port=3306 --mysql-user=root --mysql-password=admin --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=120 --report-interval=10 prepare
$ sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=1.1.1.144 --mysql-port=3306 --mysql-user=root --mysql-password=admin --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=120 --report-interval=10 run

[ 10s ] thds: 10 tps: 190.00 qps: 3814.31 (r/w/o: 2671.71/761.50/381.10) lat (ms,95%): 80.03 err/s: 0.10 reconn/s: 0.00 [ 20s ] thds: 10 tps: 199.01 qps: 3985.03 (r/w/o: 2789.86/797.15/398.02) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 10 tps: 188.60 qps: 3773.54 (r/w/o: 2640.36/755.99/377.19) lat (ms,95%): 74.46 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 10 tps: 189.40 qps: 3785.60 (r/w/o: 2650.60/756.20/378.80) lat (ms,95%): 80.03 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 10 tps: 159.09 qps: 3174.80 (r/w/o: 2222.89/633.74/318.17) lat (ms,95%): 132.49 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 10 tps: 170.51 qps: 3421.26 (r/w/o: 2393.88/686.25/341.13) lat (ms,95%): 87.56 err/s: 0.10 reconn/s: 0.00 [ 70s ] thds: 10 tps: 179.80 qps: 3591.35 (r/w/o: 2514.63/717.01/359.70) lat (ms,95%): 77.19 err/s: 0.10 reconn/s: 0.00 [ 80s ] thds: 10 tps: 160.03 qps: 3189.93 (r/w/o: 2231.57/638.51/319.85) lat (ms,95%): 92.42 err/s: 0.00 reconn/s: 0.00 [ 90s ] thds: 10 tps: 189.29 qps: 3802.83 (r/w/o: 2662.88/761.17/378.78) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00 [ 100s ] thds: 10 tps: 188.79 qps: 3768.11 (r/w/o: 2638.46/752.06/377.58) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00 [ 110s ] thds: 10 tps: 182.83 qps: 3664.28 (r/w/o: 2564.21/734.42/365.66) lat (ms,95%): 74.46 err/s: 0.00 reconn/s: 0.00 [ 120s ] thds: 10 tps: 173.17 qps: 3459.17 (r/w/o: 2422.73/689.99/346.45) lat (ms,95%): 90.78 err/s: 0.20 reconn/s: 0.00 SQL statistics: queries performed: read: 304080 write: 86869 other: 43435 total: 434384 transactions: 21715 (180.90 per sec.) queries: 434384 (3618.79 per sec.) ignored errors: 5 (0.04 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 120.0339s total number of events: 21715 Latency (ms): min: 17.49 avg: 55.27 max: 547.58 95th percentile: 80.03 sum: 1200106.85 Threads fairness: events (avg/stddev): 2171.5000/8.20 execution time (avg/stddev): 120.0107/0.01

0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

[ 10s ] thds: 10 tps: 190.00 qps: 3814.31 (r/w/o: 2671.71/761.50/381.10) lat (ms,95%): 80.03 err/s: 0.10 reconn/s: 0.00
[ 20s ] thds: 10 tps: 199.01 qps: 3985.03 (r/w/o: 2789.86/797.15/398.02) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 10 tps: 188.60 qps: 3773.54 (r/w/o: 2640.36/755.99/377.19) lat (ms,95%): 74.46 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 10 tps: 189.40 qps: 3785.60 (r/w/o: 2650.60/756.20/378.80) lat (ms,95%): 80.03 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 10 tps: 159.09 qps: 3174.80 (r/w/o: 2222.89/633.74/318.17) lat (ms,95%): 132.49 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 10 tps: 170.51 qps: 3421.26 (r/w/o: 2393.88/686.25/341.13) lat (ms,95%): 87.56 err/s: 0.10 reconn/s: 0.00
[ 70s ] thds: 10 tps: 179.80 qps: 3591.35 (r/w/o: 2514.63/717.01/359.70) lat (ms,95%): 77.19 err/s: 0.10 reconn/s: 0.00
[ 80s ] thds: 10 tps: 160.03 qps: 3189.93 (r/w/o: 2231.57/638.51/319.85) lat (ms,95%): 92.42 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 10 tps: 189.29 qps: 3802.83 (r/w/o: 2662.88/761.17/378.78) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 10 tps: 188.79 qps: 3768.11 (r/w/o: 2638.46/752.06/377.58) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 10 tps: 182.83 qps: 3664.28 (r/w/o: 2564.21/734.42/365.66) lat (ms,95%): 74.46 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 10 tps: 173.17 qps: 3459.17 (r/w/o: 2422.73/689.99/346.45) lat (ms,95%): 90.78 err/s: 0.20 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            304080
        write:                           86869
        other:                           43435
        total:                           434384
    transactions:                        21715  (180.90 per sec.)
    queries:                             434384 (3618.79 per sec.)
    ignored errors:                      5      (0.04 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.0339s
    total number of events:              21715

Latency (ms):
         min:                                   17.49
         avg:                                   55.27
         max:                                  547.58
         95th percentile:                       80.03
         sum:                              1200106.85

Threads fairness:
    events (avg/stddev):           2171.5000/8.20
    execution time (avg/stddev):   120.0107/0.01

MySQL8.0.11 测试负载5.80

$ sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=1.1.1.143 --mysql-port=3306 --mysql-user=root --mysql-password=admin --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=120 --report-interval=10 prepare $ sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=1.1.1.143 --mysql-port=3306 --mysql-user=root --mysql-password=admin --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=120 --report-interval=10 run

0
1

$ sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=1.1.1.143 --mysql-port=3306 --mysql-user=root --mysql-password=admin --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=120 --report-interval=10 prepare
$ sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=1.1.1.143 --mysql-port=3306 --mysql-user=root --mysql-password=admin --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=120 --report-interval=10 run

[ 10s ] thds: 10 tps: 58.57 qps: 1190.44 (r/w/o: 834.01/238.29/118.14) lat (ms,95%): 646.19 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 10 tps: 93.50 qps: 1862.99 (r/w/o: 1305.69/370.30/187.00) lat (ms,95%): 383.33 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 10 tps: 96.60 qps: 1933.52 (r/w/o: 1353.12/387.10/193.30) lat (ms,95%): 337.94 err/s: 0.10 reconn/s: 0.00 [ 40s ] thds: 10 tps: 113.60 qps: 2271.81 (r/w/o: 1590.20/454.40/227.20) lat (ms,95%): 155.80 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 10 tps: 102.10 qps: 2044.52 (r/w/o: 1432.01/408.30/204.20) lat (ms,95%): 369.77 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 10 tps: 118.40 qps: 2365.95 (r/w/o: 1655.37/473.79/236.80) lat (ms,95%): 196.89 err/s: 0.00 reconn/s: 0.00 [ 70s ] thds: 10 tps: 126.70 qps: 2534.55 (r/w/o: 1774.74/506.41/253.41) lat (ms,95%): 142.39 err/s: 0.00 reconn/s: 0.00 [ 80s ] thds: 10 tps: 123.20 qps: 2470.22 (r/w/o: 1727.65/496.18/246.39) lat (ms,95%): 155.80 err/s: 0.00 reconn/s: 0.00 [ 90s ] thds: 10 tps: 66.40 qps: 1323.21 (r/w/o: 926.31/264.00/132.90) lat (ms,95%): 427.07 err/s: 0.10 reconn/s: 0.00 [ 100s ] thds: 10 tps: 89.76 qps: 1788.72 (r/w/o: 1251.65/358.04/179.02) lat (ms,95%): 282.25 err/s: 0.00 reconn/s: 0.00 [ 110s ] thds: 10 tps: 93.84 qps: 1882.25 (r/w/o: 1319.79/374.27/188.18) lat (ms,95%): 350.33 err/s: 0.00 reconn/s: 0.00 [ 120s ] thds: 10 tps: 73.66 qps: 1479.03 (r/w/o: 1033.59/298.12/147.31) lat (ms,95%): 383.33 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 162064 write: 46299 other: 23150 total: 231513 transactions: 11574 (96.13 per sec.) queries: 231513 (1922.88 per sec.) ignored errors: 2 (0.02 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 120.3951s total number of events: 11574 Latency (ms): min: 11.85 avg: 103.98 max: 1284.28 95th percentile: 292.60 sum: 1203506.94 Threads fairness: events (avg/stddev): 1157.4000/7.42 execution time (avg/stddev): 120.3507/0.05

0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

[ 10s ] thds: 10 tps: 58.57 qps: 1190.44 (r/w/o: 834.01/238.29/118.14) lat (ms,95%): 646.19 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 10 tps: 93.50 qps: 1862.99 (r/w/o: 1305.69/370.30/187.00) lat (ms,95%): 383.33 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 10 tps: 96.60 qps: 1933.52 (r/w/o: 1353.12/387.10/193.30) lat (ms,95%): 337.94 err/s: 0.10 reconn/s: 0.00
[ 40s ] thds: 10 tps: 113.60 qps: 2271.81 (r/w/o: 1590.20/454.40/227.20) lat (ms,95%): 155.80 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 10 tps: 102.10 qps: 2044.52 (r/w/o: 1432.01/408.30/204.20) lat (ms,95%): 369.77 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 10 tps: 118.40 qps: 2365.95 (r/w/o: 1655.37/473.79/236.80) lat (ms,95%): 196.89 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 10 tps: 126.70 qps: 2534.55 (r/w/o: 1774.74/506.41/253.41) lat (ms,95%): 142.39 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 10 tps: 123.20 qps: 2470.22 (r/w/o: 1727.65/496.18/246.39) lat (ms,95%): 155.80 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 10 tps: 66.40 qps: 1323.21 (r/w/o: 926.31/264.00/132.90) lat (ms,95%): 427.07 err/s: 0.10 reconn/s: 0.00
[ 100s ] thds: 10 tps: 89.76 qps: 1788.72 (r/w/o: 1251.65/358.04/179.02) lat (ms,95%): 282.25 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 10 tps: 93.84 qps: 1882.25 (r/w/o: 1319.79/374.27/188.18) lat (ms,95%): 350.33 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 10 tps: 73.66 qps: 1479.03 (r/w/o: 1033.59/298.12/147.31) lat (ms,95%): 383.33 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            162064
        write:                           46299
        other:                           23150
        total:                           231513
    transactions:                        11574  (96.13 per sec.)
    queries:                             231513 (1922.88 per sec.)
    ignored errors:                      2      (0.02 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.3951s
    total number of events:              11574

Latency (ms):
         min:                                   11.85
         avg:                                  103.98
         max:                                 1284.28
         95th percentile:                      292.60
         sum:                              1203506.94

Threads fairness:
    events (avg/stddev):           1157.4000/7.42
    execution time (avg/stddev):   120.3507/0.05