mysql数据库安装2017_mysql数据库的安装

1)软件下载

https://cdn.mysql.com//Downloads/MySQL-5.6/mysql-5.6.38-linux-glibc2.12-x86_64.tar.gz

https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

2) 确保mysql没有被修改

[root@mysqlmaster01 software]# md5sum mysql-5.6.38-linux-glibc2.12-x86_64.tar.gz

76e23e0e3d1b738da8c6db6aeb475fe0 mysql-5.6.38-linux-glibc2.12-x86_64.tar.gz

第一种：安装mysql-5.6.38-linux-glibc2.12-x86_64.tar.gz

[root@mysqlmaster01 local]# yum -y install libaio

[root@mysqlmaster01 ~]# groupadd mysql

[root@mysqlmaster01 ~]# useradd -r -g mysql -s /sbin/nologin mysql

[root@mysqlmaster01 ~]# cd /software

[root@mysqlmaster01 software]# tar xf mysql-5.6.38-linux-glibc2.12-x86_64.tar.gz -C /usr/local/

[root@mysqlmaster01 software]# cd /usr/local/

[root@mysqlmaster01 local]# ln -sv mysql-5.6.38-linux-glibc2.12-x86_64/ mysql

`mysql' -> `mysql-5.6.38-linux-glibc2.12-x86_64/'

[root@mysqlmaster01 local]# ll mysql

lrwxrwxrwx. 1 root root 36 Nov 21 09:28 mysql -> mysql-5.6.38-linux-glibc2.12-x86_64/

[root@mysqlmaster01 local]# cd mysql

[root@mysqlmaster01 mysql]# chown -R root.mysql .

编辑配置文件

[root@mysqlmaster01 ~]# vim /etc/my.cnf

[mysqld]

port = 3306

socket = /tmp/mysql.sock

skip-external-locking

key_buffer_size = 256M

max_allowed_packet = 1M

table_open_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 16M

datadir=/usr/local/mysql/data

innodb_log_file_size = 1G

log_error = error.log

skip_name_resolve = 1

bind_address = 10.2.11.226

初始化mysql数据库

[root@mysqlmaster01 mysql]# scripts/mysql_install_db --user=mysql

2017-11-21 11:32:54 0 [Note] ./bin/mysqld (mysqld 5.6.38-log) starting as process 2457 ...

OK

Filling help tables...2017-11-21 11:34:04 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.

2017-11-21 11:34:04 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2017-11-21 11:34:04 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.

2017-11-21 11:34:04 0 [Note] ./bin/mysqld (mysqld 5.6.38-log) starting as process 2480 ...

OK

查看默认数据库有没有

[root@mysqlmaster01 mysql]# ll -h data/

total 2.1G

-rw-rw----. 1 mysql mysql 3.6K Nov 21 11:34 error.log

-rw-rw----. 1 mysql mysql 12M Nov 21 11:34 ibdata1

-rw-rw----. 1 mysql mysql 1.0G Nov 21 11:34 ib_logfile0

-rw-rw----. 1 mysql mysql 1.0G Nov 21 11:33 ib_logfile1

drwx------. 2 mysql mysql 4.0K Nov 21 11:34 mysql

-rw-rw----. 1 mysql mysql 66K Nov 21 11:34 mysql-bin.000001

-rw-rw----. 1 mysql mysql 1.4M Nov 21 11:34 mysql-bin.000002

-rw-rw----. 1 mysql mysql 38 Nov 21 11:34 mysql-bin.index

drwx------. 2 mysql mysql 4.0K Nov 21 11:34 performance_schema

drwx------. 2 mysql mysql 4.0K Nov 21 11:32 test

[root@mysqlmaster01 mysql]# chown -R mysql data/ (修改data目录的权限属主为mysql)

查看最终mysql目录的权限如下，除了data目录以外，其他目录的属主为root，属组为mysql

[root@mysqlmaster01 mysql]# ll

total 76

drwxr-xr-x. 2 root mysql 4096 Nov 21 09:25 bin

-rw-r--r--. 1 root mysql 17987 Sep 13 23:49 COPYING

drwxr-xr-x. 5 mysql mysql 4096 Nov 21 09:49 data

drwxr-xr-x. 2 root mysql 4096 Nov 21 09:25 docs

drwxr-xr-x. 3 root mysql 4096 Nov 21 09:25 include

drwxr-xr-x. 3 root mysql 4096 Nov 21 09:25 lib

drwxr-xr-x. 4 root mysql 4096 Nov 21 09:25 man

-rw-r--r--. 1 root mysql 943 Nov 21 09:44 my.cnf

-rw-r--r--. 1 root mysql 943 Nov 21 09:49 my-new.cnf

drwxr-xr-x. 10 root mysql 4096 Nov 21 09:25 mysql-test

-rw-r--r--. 1 root mysql 2496 Sep 13 23:49 README

drwxr-xr-x. 2 root mysql 4096 Nov 21 09:25 scripts

drwxr-xr-x. 28 root mysql 4096 Nov 21 09:25 share

drwxr-xr-x. 4 root mysql 4096 Nov 21 09:25 sql-bench

drwxr-xr-x. 2 root mysql 4096 Nov 21 09:25 support-files

拷贝mysqld的启动文件

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

[root@mysqlmaster01 mysql]# chmod +x /etc/init.d/mysqld

[root@mysqlmaster01 mysql]# chkconfig --add mysqld

[root@mysqlmaster01 mysql]# chkconfig mysqld on

添加PATH路径

[root@mysqlmaster01 mysql]# vim /etc/profile.d/mysqld.sh

[root@mysqlmaster01 mysql]# cat /etc/profile.d/mysqld.sh

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

[root@mysqlmaster01 mysql]# source /etc/profile.d/mysqld.sh

启动mysql服务

[root@mysqlmaster01 ~]# service mysqld start

Starting MySQL. SUCCESS!

[root@mysqlmaster01 ~]# ss -tunlp|grep 3306

tcp LISTEN 0 80 10.2.11.226:3306 *:* users:(("mysqld",2883,11))

[root@mysqlmaster01 ~]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.38-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

设置mysql服务器的root密码：

[root@mysqlmaster01 ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL

SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current

password for the root user. If you've just installed MySQL, and

you haven't set the root password yet, the password will be blank,

so you should just press enter here.

Enter current password for root (enter for none):

OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL

root user without the proper authorisation.

Set root password? [Y/n] y (是否设置root用户密码)

New password:

Re-enter new password:

Password updated successfully!

Reloading privilege tables..

... Success!

By default, a MySQL installation has an anonymous user, 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? [Y/n] y (是否移除匿名用户)

... Success!

Normally, root should only be allowed to connect from 'localhost'. This

ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y (是否禁止root用户登录)

... Success!

By default, MySQL comes with a database named 'test' that 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? [Y/n] y (是否移除test数据库)

- Dropping test database...

... Success!

- Removing privileges on test database...

... Success!

Reloading the privilege tables will ensure that all changes made so far

will take effect immediately.

Reload privilege tables now? [Y/n] y

... Success!

All done! If you've completed all of the above steps, your MySQL

installation should now be secure.

Thanks for using MySQL!

Cleaning up...

测试登录mysql

[root@mysqlmaster01 ~]# mysql -u root -p

Enter password: (输入root用户的密码)

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 13

Server version: 5.6.38-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

错误1：

[root@mysqlmaster01 mysql]# scripts/mysql_install_db --user=mysql

Installing MySQL system tables..../bin/mysqld: error while loading shared libraries: libnuma.so.1: cannot open shared object file: No such file or directory

解决办法

[root@mysqlmaster01 mysql]# yum provides libnuma.so.1

Loaded plugins: fastestmirror

Loading mirror speeds from cached hostfile

* base: mirrors.aliyun.com

* epel: mirrors.aliyun.com

* extras: mirrors.aliyun.com

* updates: mirrors.aliyun.com

numactl-2.0.9-2.el6.i686 : Library for tuning for Non Uniform Memory Access machines

Repo : base

Matched from:

Other : libnuma.so.1

[root@mysqlmaster01 mysql]# yum -y install numactl

第二种：安装mysql-5.7的版本

[root@mysqlmaster01 software]#yum install libaio

[root@mysqlmaster01 software]# tar xf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz -C /usr/local/

[root@mysqlmaster01 software]# cd /usr/local/

[root@mysqlmaster01 local]# ln -sv mysql-5.7.20-linux-glibc2.12-x86_64/ mysql-5.7

`mysql-5.7' -> `mysql-5.7.20-linux-glibc2.12-x86_64/'

[root@mysqlmaster01 local]# cd mysql-5.7

[root@mysqlmaster01 mysql-5.7]# ll

total 52

drwxr-xr-x. 2 root root 4096 Nov 21 11:45 bin

-rw-r--r--. 1 7161 31415 17987 Sep 13 23:48 COPYING

drwxr-xr-x. 2 root root 4096 Nov 21 11:45 docs

drwxr-xr-x. 3 root root 4096 Nov 21 11:45 include

drwxr-xr-x. 5 root root 4096 Nov 21 11:45 lib

drwxr-xr-x. 4 root root 4096 Nov 21 11:45 man

-rw-r--r--. 1 7161 31415 2478 Sep 13 23:48 README

drwxr-xr-x. 28 root root 4096 Nov 21 11:45 share

drwxr-xr-x. 2 root root 4096 Nov 21 11:45 support-files

[root@mysqlmaster01 ~]# groupadd mysql

[root@mysqlmaster01 ~]# useradd -r -g mysql -s /sbin/nologin mysql

[root@mysqlmaster01 mysql-5.7]# mkdir mysql-files

[root@mysqlmaster01 mysql-5.7]# chmod 750 mysql-files/

[root@mysqlmaster01 mysql-5.7]# chown -R root.mysql  .

编写配置文件

[root@mysqlmaster01 mysql-5.7]# vim /etc/my.cnf

[client]

user=david

password=88888888

[mysqld]

########basic settings########

server-id = 11

port = 3306

user = mysql

bind_address = 10.2.11.226

autocommit = 0

character_set_server=utf8mb4

skip_name_resolve = 1

max_connections = 800

max_connect_errors = 1000

#datadir = /usr/local/mysql-5.7/data

datadir=/data/mysql_data

transaction_isolation = READ-COMMITTED

explicit_defaults_for_timestamp = 1

join_buffer_size = 134217728

tmp_table_size = 67108864

tmpdir = /tmp

max_allowed_packet = 16777216

sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"

interactive_timeout = 1800

wait_timeout = 1800

read_buffer_size = 16777216

read_rnd_buffer_size = 33554432

sort_buffer_size = 33554432

########log settings########

log_error = error.log

slow_query_log = 1

slow_query_log_file = slow.log

log_queries_not_using_indexes = 1

log_slow_admin_statements = 1

log_slow_slave_statements = 1

log_throttle_queries_not_using_indexes = 10

expire_logs_days = 90

long_query_time = 2

min_examined_row_limit = 100

########replication settings########

master_info_repository = TABLE

relay_log_info_repository = TABLE

log_bin = bin.log

sync_binlog = 1

gtid_mode = on

enforce_gtid_consistency = 1

log_slave_updates

binlog_format = row

relay_log = relay.log

relay_log_recovery = 1

binlog_gtid_simple_recovery = 1

slave_skip_errors = ddl_exist_errors

########innodb settings########

innodb_page_size = 8192

innodb_buffer_pool_size = 6G

innodb_buffer_pool_instances = 8

innodb_buffer_pool_load_at_startup = 1

innodb_buffer_pool_dump_at_shutdown = 1

innodb_lru_scan_depth = 2000

innodb_lock_wait_timeout = 5

innodb_io_capacity = 4000

innodb_io_capacity_max = 8000

innodb_flush_method = O_DIRECT

innodb_file_format = Barracuda

innodb_file_format_max = Barracuda

innodb_log_group_home_dir = /redolog/

innodb_undo_directory = /undolog/

innodb_undo_logs = 128

innodb_undo_tablespaces = 3

innodb_flush_neighbors = 1

innodb_log_file_size = 1G

innodb_log_buffer_size = 16777216

innodb_purge_threads = 4

innodb_large_prefix = 1

innodb_thread_concurrency = 64

innodb_print_all_deadlocks = 1

innodb_strict_mode = 1

innodb_sort_buffer_size = 67108864

########semi sync replication settings########

plugin_dir=/usr/local/mysql/lib/plugin

plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

loose_rpl_semi_sync_master_enabled = 1

loose_rpl_semi_sync_slave_enabled = 1

loose_rpl_semi_sync_master_timeout = 5000

初始化数据库

[root@mysqlmaster01 mysql-5.7]# bin/mysqld --initialize --user=mysql

[root@mysqlmaster01 mysql-5.7]# tail -f /data/mysql_data/error.log

2017-11-21T06:29:26.573594Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2017-11-21T06:29:26.581355Z 1 [Note] A temporary password is generated for root@localhost: 6Xs7KM#r2(ee (初始mysql密码)

2017-11-21T06:29:29.584109Z 1 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.

2017-11-21T06:29:29.584150Z 1 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.

2017-11-21T06:29:29.584167Z 1 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.

2017-11-21T06:29:29.584190Z 1 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.

2017-11-21T06:29:29.584199Z 1 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.

2017-11-21T06:29:29.584211Z 1 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.

2017-11-21T06:29:29.584256Z 1 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.

2017-11-21T06:29:29.584270Z 1 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.

mysql_ssl_rsa_setup需要openssl支持，用于启用数据量ssl连接，需要进一步配置。

[root@mysqlmaster01 mysql-5.7]# bin/mysql_ssl_rsa_setup

Generating a 2048 bit RSA private key

............................+++

...........+++

writing new private key to 'ca-key.pem'

-----

Generating a 2048 bit RSA private key

...........................+++

................................................................................................................................+++

writing new private key to 'server-key.pem'

-----

Generating a 2048 bit RSA private key

......+++

.....................................+++

writing new private key to 'client-key.pem'

-----

授权mysql用户对data目录和mysql_files目录有读写权限

[root@mysqlmaster01 mysql-5.7]# chown -R mysql mysql-files/

[root@mysqlmaster01 ~]# chown -R mysql.mysql /data/mysql_data

测试启动mysql、登录mysql

[root@mysqlmaster01 mysql-5.7]# bin/mysqld_safe --user=mysql &

[2] 3909

[root@mysqlmaster01 mysql-5.7]# 2017-11-21T05:06:12.720930Z mysqld_safe Logging to '/usr/local/mysql-5.7/data/error.log'.

2017-11-21T05:06:12.835612Z mysqld_safe A mysqld process already exists

[2]- Exit 1 bin/mysqld_safe --user=mysql

[root@mysqlmaster01 mysql-5.7]#

[root@mysqlmaster01 mysql-5.7]#

[root@mysqlmaster01 mysql-5.7]#

[root@mysqlmaster01 mysql-5.7]# ss -tunlp|grep 3306

tcp LISTEN 0 80 10.2.11.226:3306 *:* users:(("mysqld",3880,23))

[root@mysqlmaster01 mysql-5.7]# mysql -u root -p

Enter password:6Xs7KM#r2(ee

(2017-11-21T04:09:23.258462Z 1 [Note] A temporary password is generated for root@localhost: N&owf4C8n/M?)

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.20-log

Copyright (c) 2000, 2017, 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.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set password=PASSWORD('Aa123456');(修改root用户的密码)

Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys |

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

4 rows in set (0.00 sec)

添加到MySQL 启动脚本到系统服务

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

[root@mysqlmaster01 mysql-5.7]# chkconfig --add mysqld-5.7

[root@mysqlmaster01 mysql-5.7]# chkconfig mysqld-5.7 on

更改/etc/init.d/mysqld-5.7

[root@mysqlmaster01 mysql-5.7]# vim /etc/init.d/mysqld-5.7

把datadir=/usr/local/mysql/data更改成datadir=/data/mysql_data

[root@mysqlmaster01 mysql-5.7]# service mysqld-5.7 start

Starting MySQL SUCCESS!

编写path环境变量

[root@mysqlmaster01 mysql]# vim /etc/profile.d/mysqld.sh

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

[root@mysqlmaster01 mysql]# source /etc/profile.d/mysqld.sh

关于mysql配置文件的介绍

[root@mysqlmaster01 mysql]# mysqld -V

mysqld Ver 5.7.20 for linux-glibc2.12 on x86_64 (MySQL Community Server (GPL))

[root@mysqlmaster01 mysql]# mysqld -v --help|grep my.cnf

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

my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default

(如果一个配置在上面多个文件同时存在，那么越往后面的，优先级越高，如果配置的参数没有生效，看看是不是发生了参数覆盖的问题)

[root@mysqlmaster01 mysql]# mysqld -v --help|grep defaults-file

--defaults-file=# Only read default options from the given file #.

通过--defaults-file指定配置文件