CentOS6.5上部署MySQL5.7.17⼆进制安装以及多实例配置
1、建⽤户、下载、解压
groupadd mysql
useradd -r -g mysql mysql
wget sql/get/Downloads/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_
tar xvf mysql-5.7.17-linux-glibc2.5-x86_ -C /usr/local/
ln -sv /usr/local/mysql-5.7.17-linux-glibc2.5-x86_64 /usr/local/mysql
2、输出环境变量、帮助
/etc/profile
export PATH=$PATH:/usr/local/mysql/bin
source /etc/profile
vim /fig
MANPATH /usr/local/mysql/man
3、创建数据⽬录、授权、初始化mysql mysql5.7.7及以上做了很多改变，5.7.7以前安装⽅法和以前差不多，初始化也保留了mysql_install_db，5.7.7以后则去掉了该脚本，使⽤了-initialize 或者 --
initialize-insecure 参数作为初始化。
mysql5.7.14版本初始化时候已经抛弃了defaults-file参数⽂件，所以在初始化时候指定配置⽂件会出错，同时必须保证datadir为空。
mkdir /data/mysql
sql /data/mysql
[root@leo mysql]# mysqld --verbose --help |more 查看更多初始化参数
[root@leo mysql]# /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql
2016-12-21T09:37:13.532770Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-12-21T09:37:16.364569Z 0 [Warning] InnoDB: New log files created, LSN=45790
2016-12-21T09:37:16.881727Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2016-12-21T09:37:17.115686Z 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: 0fbca93f-c761-11e6-9409-000c299a8601.
2016-12-21T09:37:17.220886Z 0 [Warning] Gtid table is not ready to be used. Table 'id_executed' cannot be opened.
2016-12-21T09:37:17.284087Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
初始化成功了。这⾥说明下，初始化参数我使⽤了--initialize-insecure，这样不会设置初始化root密码，如果是 --initialize的话，会随机⽣成⼀个密码：
4、设置加密连接
[root@leo mysql]# /usr/local/mysql/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'
-----
5、复制配置⽂件和启动脚本
[root@leo mysql]# cp /usr/local/mysql/support-files/my-defaultf /etc/myf
[root@leo mysql]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
6、配置如下
[root@leo mysql]# cat /etc/myf
# For advice on how to change settings please see
# sql/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[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/mysql
port=3306
server_id=1
socket=/data/mysql/mysql.sock
symbolic-links=0
character_set_server=utf8
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[client]
socket=/data/mysql/mysql.sock
7、启动、并查看⽇志
[root@leo ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/'.
. [确定]
[root@leo ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. [确定]
Starting MySQL.. [确定]
[root@leo ~]# tail -f /data/
2016-12-21T09:42:48.493804Z 0 [Note] - '::' resolves to '::';
2016-12-21T09:42:48.493834Z 0 [Note] Server socket created on IP: '::'.
2016-12-21T09:42:48.493990Z 0 [Note] InnoDB: Loading buffer pool(s) from /data/mysql/ib_buffer_pool
2016-12-21T09:42:48.496446Z 0 [Note] InnoDB: Buffer pool(s) load completed at 161221 17:42:48
2016-12-21T09:42:48.512719Z 0 [Note] Event Scheduler: Loaded 0 events
2016-12-21T09:42:48.512907Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option '--disable-partition-engine-check' to skip this check. 2016-12-21T09:42:48.512922Z 0 [Note] Beginning of list of non-natively partitioned tables
2016-12-21T09:42:48.529189Z 0 [Note] End of list of non-natively partitioned tables
2016-12-21T09:42:48.529703Z 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.7.17' socket: '/data/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
8、登录测试
[root@leo mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17 MySQL Community Server (GPL)
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.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql>
[root@leo mysql]# ps -ef |grep mysql
root 49305 1 0 17:54 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/leo.pid
mysql 49501 49305 0 17:54 pts/0 00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/ --pid-file=/data/mysql/leo.pid --socket=/data/mysql/mysq root 49598 46306 0 18:14 pts/0 00:00:00 grep mysql
多实例配置部分
1、复制多实例启动脚本
[root@leo ~]# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi
2、初始化数据库⽬录并修改多实例配置⽂件
mkdir /data/mysql2
mkdir /data/mysql3
sql /data/mysql2
sql /data/mysql3
[root@leo ~]# /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql2
2016-12-21T16:36:00.886650Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-12-21T16:36:06.481686Z 0 [Warning] InnoDB: New log files created, LSN=45790
2016-12-21T16:36:07.145444Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2016-12-21T16:36:07.443823Z 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: 92945fc8-c79b-11e6-88a5-000c299a8601.
2016-12-21T16:36:07.453113Z 0 [Warning] Gtid table is not ready to be used. Table 'id_executed' cannot be opened.
2016-12-21T16:36:07.456819Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@leo ~]# /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql3
2016-12-21T16:36:16.094948Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-12-21T16:36:21.224144Z 0 [Warning] InnoDB: New log files created, LSN=45790
2016-12-21T16:36:21.900500Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2016-12-21T16:36:22.095535Z 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: 9b500f71-c79b-11e6-8af0-000c299a8601.
2016-12-21T16:36:22.105950Z 0 [Warning] Gtid table is not ready to be used. Table 'id_executed' cannot be opened.
2016-12-21T16:36:22.112685Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@leo ~]# cat /etc/myf
# For advice on how to change settings please see
# sql/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe
mysqladmin=/usr/local/mysql/bin/mysqladmin
log=/data/mysql_multi.log
user = root
password = redhat
# 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.
[mysqld1]
basedir=/usr/local/mysql
datadir=/data/mysql
port=3306
server_id=1
socket=/data/mysql/mysql.sock
symbolic-links=0
character_set_server=utf8
pid-file=/data/mysql/mysql.pid
[mysqld2]
datadir=/data/mysql2
port=3307
socket=/data/mysql2/mysql.sock
symbolic-links=0
character_set_server=utf8
pid-file=/data/mysql2/mysql2.pid
[mysqld3]
datadir=/data/mysql3
port=3308
socket=/data/mysql3/mysql.sock
symbolic-links=0
character_set_server=utf8
pid-file=/data/mysql3/mysql3.pid
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#[client]
#socket=/data/mysql/mysql.sock
3、启动多实例测试并查看⽇志
[root@leo ~]# /etc/init.d/mysqld_multi start
[root@leo ~]# /etc/init.d/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
[root@leo ~]# netstat -antlp |grep :330*
tcp 0 0 :::3307 :::* LISTEN 55762/mysqld
tcp 0 0 :::3308 :::* LISTEN 55765/mysqld
tcp 0 0 :::3306 :::* LISTEN 55764/mysqld
4、连接测试
[root@leo ~]# mysql -uroot -h127.0.0.1 -P3308 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.17 MySQL Community Server (GPL)
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.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> grant shutdown on *.* to 'root'@'%' identified by 'redhat'; #授权
Query OK, 0 rows affected, 1 warning (0.08 sec)
mysql>flush provileges;
update mysql.user set authentication_string=password('redhat') where user='root' and Host = 'localhost';#设置mysql登录密码
[root@leo ~]# mysqladmin -uroot -p -S /data/mysql3/mysql.sock shutdown #关闭实例
Enter password:
5、附带脚本多实例管理脚本
stop关闭MySQL实例，注意此处是需要⼀个具有shutdown权限的⽤户，且密码并被是加密的，也不可以交互式输⼊密码，Linux⼜具有history功能，所以为了数据库的安全，还是不要⽤mysqld_multi
stop的⽅式关闭数据库了吧
[root@leo ~]# cat /etc/init.d/mysqld_multi
#!/bin/sh
#
# A simple startup script for mysqld_multi by Tim Smith and Jani Tolonen.
# This script assumes that myf file exists either in /etc/myf or
# /root/.myf and has groups [mysqld_multi] and [mysqldN]. See the
# mysqld_multi documentation for detailed instructions.
#
# This script can be used as /etc/init.d/mysql.server
#
# Comments to support chkconfig on RedHat Linuxmysql下载starting the server
# chkconfig: 2345 64 36
# description: A very fast and reliable SQL database engine.
#
# Version 1.0
#
basedir=/usr/local/mysql
bindir=/usr/local/mysql/bin
conf=/etc/myf
if test -x $bindir/mysqld_multi
then
mysqld_multi="$bindir/mysqld_multi";
else
echo "Can't execute $bindir/mysqld_multi from dir $basedir";
exit;
fi
case "$1" in
'start' )
"$mysqld_multi" --defaults-extra-file=$conf start $2
;;
'stop' )
"$mysqld_multi" --defaults-extra-file=$conf stop $2 --user=root --password=redhat
;;
'report' )
"$mysqld_multi" --defaults-extra-file=$conf report $2
;;
'restart' )
"$mysqld_multi" --defaults-extra-file=$conf stop $2 --user=root --password=redhat
"$mysqld_multi" --defaults-extra-file=$conf start $2
;;
*)
echo "Usage: $0 {start|stop|report|restart}" >&2
;;
esac
6、多实例测试启停
[root@leo ~]# /etc/init.d/mysqld_multi stop
[root@leo ~]# /etc/init.d/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is not running
[root@leo ~]# /etc/init.d/mysqld_multi start
[root@leo ~]# /etc/init.d/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
[root@leo ~]# netstat -antlp |grep :330* #查看监听端⼝
tcp 0 0 :::3307 :::* LISTEN 74667/mysqld
tcp 0 0 :::3308 :::* LISTEN 74666/mysqld
tcp 0 0 :::3306 :::* LISTEN 74665/mysqld
[root@leo ~]# mysql -uroot -predhat -P3307 -h127.0.0.1 #登录3307 测试
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.17 MySQL Community Server (GPL)
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.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| t1 |
+--------------------+
5 rows in set (0.13 sec)
mysql>
[root@leo ~]# tail -f /data/ #查看⽇志
2016-12-21T21:47:56.114139Z 0 [Note] Server hostname (bind-address): '*'; port: 3308
2016-12-21T21:47:56.145404Z 0 [Note] IPv6 is available.
2016-12-21T21:47:56.169487Z 0 [Note] - '::' resolves to '::';
2016-12-21T21:47:56.171033Z 0 [Note] Server socket created on IP: '::'.
2016-12-21T21:47:57.157171Z 0 [Note] Event Scheduler: Loaded 0 events
2016-12-21T21:47:57.157710Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option '--disable-partition-engine-check' to skip this check. 2016-12-21T21:47:57.
157729Z 0 [Note] Beginning of list of non-natively partitioned tables
2016-12-21T21:47:58.138317Z 0 [Note] End of list of non-natively partitioned tables
2016-12-21T21:47:58.138474Z 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.7.17' socket: '/data/mysql3/mysql.sock' port: 3308 MySQL Community Server (GPL)
mysqld_multi启动会查myf⽂件中的[mysqldN]组，N为mysqld_multi后携带的整数值。 mysqld_multi的固定选项可在配置⽂件myf中进⾏配置，在[mysqld_multi]组下配置（如果没有该组，可⾃⾏
建⽴）。 mysqld_multi使⽤⽅式如下： mysqld_multi [options] {start|stop|reload|report} [GNR[,GNR] ...]
以上所述是⼩编给⼤家介绍的CentOS6.5 上部署 MySQL5.7.17 ⼆进制安装以及多实例配置，希望对⼤家有所帮助，如果⼤家有任何疑问请给我留⾔，⼩编会及时回复⼤家的。在此也⾮常感谢⼤家对⽹站的⽀持！
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论