mysql初始化新实例_2mysql初始化及多实例案例

1初始化配置文件位置及说明：

初始化配置文件的位置(后面的文件会覆盖掉前面的文件)：

[root@instance-r5y0pf5d ~]# mysqld --help --verbose|grep "my.cnf"

2021-01-07T01:43:46.831576Z 0 [Note] --secure-file-priv is setto NULL. Operations related to importing and exporting data are disabled2021-01-07T01:43:46.831666Z 0 [Note] mysqld (mysqld 5.7.26) starting as process 28770...2021-01-07T01:43:46.926752Z 0 [Note] Plugin 'FEDERATED' isdisabled./etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf2021-01-07T01:43:46.937792Z 0[Note] Binlog end2021-01-07T01:43:46.937853Z 0 [Note] Shutting down plugin 'MyISAM'

2021-01-07T01:43:46.937870Z 0 [Note] Shutting down plugin 'CSV'my.cnf, $MYSQL_TCP_PORT,/etc/services, built-in default[root@instance-r5y0pf5d ~]#

mysql初始化通用模板参数说明

[mysqld]服务端

user=mysql用户名

basedir=/application/mysql软件路径

datadir=/data/mysql/data数据路径

socket=/tmp/mysql.sock本地启动文件路径

server_id=6大于1的任意数定义为服务区id

port=3306设置服务端口

log_error=/data/mysql/data/mysql.log错误日志路径

log_bin=/data/mysql/data/mysql-bin二进制错误日志路径

[mysql]客户端

socket=/tmp/mysql.sock客户端连接文件的路径

2多实例案例：

1)准备配置文件

[root@instance-r5y0pf5d ~]# mkdir -p /data/330{7,8,9}/data

[root@instance-r5y0pf5d ~]# cat > /data/3307/my.cnf<[mysqld]

> basedir=/application/mysql

> datadir=/data/3307/data

> socket=/data/3307/mysql.sock

> log_error=/data/3307/mysql.log

> log_bin=/data/3307/mysql-bin

> port=3307

> server_id=7

> EOF

[root@instance-r5y0pf5d ~]# cat > /data/3308/my.cnf<[mysqld]

> basedir=/application/mysql

> datadir=/data/3308/data

> socket=/data/3308/mysql.sock

> log_error=/data/3308/mysql.log

> log_bin=/data/3308/mysql-bin

> port=3308

> server_id=8

> EOF

[root@instance-r5y0pf5d ~]# cat > /data/3309/my.cnf<[mysqld]

> basedir=/application/mysql

> datadir=/data/3309/data

> socket=/data/3309/mysql.sock

> log_error=/data/3309/mysql.log

> log_bin=/data/3309/mysql-bin

> port=3309

> server_id=9

> EOF

[root@instance-r5y0pf5d ~]#

2)初始化三套数据：

初始化前需要先将默认的 my.cnf挪走，防止初始化失败

[root@instance-r5y0pf5d ~]# mv /etc/my.cnf /etc/my.cnf.bak

[root@instance-r5y0pf5d ~]# mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/application/mysql

2021-01-07T03:21:29.659913Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2021-01-07T03:21:30.629030Z 0 [Warning] InnoDB: New log files created, LSN=45790

2021-01-07T03:21:30.766470Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2021-01-07T03:21:30.876554Z 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: 6fac189f-5097-11eb-9415-fa270000fffa.

2021-01-07T03:21:30.878375Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2021-01-07T03:21:30.878744Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

[root@instance-r5y0pf5d ~]#

[root@instance-r5y0pf5d ~]# mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/application/mysql

2021-01-07T03:24:25.261579Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2021-01-07T03:24:26.224140Z 0 [Warning] InnoDB: New log files created, LSN=45790

2021-01-07T03:24:26.366712Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2021-01-07T03:24:26.431231Z 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: d84f9d98-5097-11eb-9a84-fa270000fffa.

2021-01-07T03:24:26.433115Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2021-01-07T03:24:26.433459Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

[root@instance-r5y0pf5d ~]#

[root@instance-r5y0pf5d ~]# mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/application/mysql

2021-01-07T03:27:16.339676Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2021-01-07T03:27:17.305843Z 0 [Warning] InnoDB: New log files created, LSN=45790

2021-01-07T03:27:17.442117Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2021-01-07T03:27:17.510814Z 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: 3e4849b8-5098-11eb-a219-fa270000fffa.

2021-01-07T03:27:17.512752Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2021-01-07T03:27:17.513095Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

[root@instance-r5y0pf5d ~]#

3)定制不同实例的启动脚本并修改启动脚本的配置文件路径：

[root@instance-r5y0pf5d ~]# cd /etc/systemd/system

[root@instance-r5y0pf5d /etc/systemd/system]# cp mysqld.service mysqld3307.service

[root@instance-r5y0pf5d /etc/systemd/system]# cp mysqld.service mysqld3308.service

[root@instance-r5y0pf5d /etc/systemd/system]# cp mysqld.service mysqld3309.service

[root@instance-r5y0pf5d /etc/systemd/system]# vim mysqld3307.service

[root@instance-r5y0pf5d /etc/systemd/system]# tail -n 2 mysqld3307.service

ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf

LimitNOFILE = 5000

[root@instance-r5y0pf5d /etc/systemd/system]# vim mysqld3308.service

[root@instance-r5y0pf5d /etc/systemd/system]# tail -n 2 mysqld3308.service|head -n1

ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf

[root@instance-r5y0pf5d /etc/systemd/system]# vim mysqld3309.service

[root@instance-r5y0pf5d /etc/systemd/system]# tail -n 2 mysqld3309.service|head -n1

ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf

[root@instance-r5y0pf5d /etc/systemd/system]#

4)授权，启动，检查

[root@instance-r5y0pf5d /etc/systemd/system]# chown -R mysql.mysql /data/*

[root@instance-r5y0pf5d /etc/systemd/system]# systemctl start mysqld3307.service

[root@instance-r5y0pf5d /etc/systemd/system]# systemctl start mysqld3308.service

[root@instance-r5y0pf5d /etc/systemd/system]# systemctl start mysqld3309.service

[root@instance-r5y0pf5d ~]# netstat -lnp|grep 330

tcp6 0 0 :::3307 :::* LISTEN 31843/mysqld

tcp6 0 0 :::3308 :::* LISTEN 31877/mysqld

tcp6 0 0 :::3309 :::* LISTEN 364/mysqld

unix 2 [ ACC ] STREAM LISTENING 709710 31843/mysqld /data/3307/mysql.sock

unix 2 [ ACC ] STREAM LISTENING 709992 31877/mysqld /data/3308/mysql.sock

unix 2 [ ACC ] STREAM LISTENING 727558 364/mysqld /data/3309/mysql.sock

[root@instance-r5y0pf5d ~]#

[root@instance-r5y0pf5d /etc/systemd/system]# mysql -S /data/3307/mysql.sock -e "select @@server_id"

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

| @@server_id |

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

| 7 |

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

[root@instance-r5y0pf5d /etc/systemd/system]# mysql -S /data/3308/mysql.sock -e "select @@server_id"

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

| @@server_id |

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

| 8 |

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

[root@instance-r5y0pf5d ~]# mysql -S /data/3309/mysql.sock -e "select @@server_id"

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

| @@server_id |

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

| 9 |

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

[root@instance-r5y0pf5d ~]#