MySQL运维—多实例

　　　　　a.多个端口
　　　　　b.多个socket文件
　　　　　c.多个日志文件
　　　　　d.多个server_id

[root@controller 3307]# cat 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
[client]
socket = /data/3307/mysql.sock

chown -R mysql.mysql /data

[root@controller scripts]# ./mysql_install_db --defaults-file=/data/3308/my.cnf --user=mysql --basedir=/application/mysql --datadir=/data/3308/data
Installing MySQL system tables...2020-09-11 16:30:10 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. 
Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-09-11 16:30:10 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2020-09-11 16:30:10 0 [Note] /application/mysql/bin/mysqld (mysqld 5.6.40-log) starting as process 1633 ...
OK

Filling help tables...2020-09-11 16:30:13 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. 
Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-09-11 16:30:13 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2020-09-11 16:30:13 0 [Note] /application/mysql/bin/mysqld (mysqld 5.6.40-log) starting as process 1655 ...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

  /application/mysql/bin/mysqladmin -u root password 'new-password'
  /application/mysql/bin/mysqladmin -u root -h controller password 'new-password'

Alternatively you can run:

  /application/mysql/bin/mysql_secure_installation

[root@controller scripts]# mysqld_safe --defaults-file=/data/3308/my.cnf &
[2] 1678
[root@controller scripts]# 200911 16:31:20 mysqld_safe Logging to '/data/3308/mysql.log'.
200911 16:31:20 mysqld_safe Starting mysqld daemon with databases from /data/3308/data

[root@controller scripts]# mysql -uroot -p -S /data/3308/mysql.sock 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.40-log MySQL Community Server (GPL)

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

[root@controller scripts]# mysqladmin -uroot -p -S /data/3307/mysql.sock password 123456
Enter password: 
Warning: Using a password on the command line interface can be insecure.

[root@controller scripts]# mysql -uroot -p -S /data/3308/mysql.sock 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.40-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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 variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 8     |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

[root@controller scripts]# vim /usr/sbin/mysql3307
mysql -uroot -p3307 -S /data/3307/mysql.sock

[root@controller scripts]# chmod +x /usr/sbin/mysql*
[root@controller scripts]# chmod 700 /usr/sbin/mysql*                         推荐用这个，只能root执行

[root@controller scripts]# mysql3307
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 2
Server version: 5.6.40-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> truncate mysql.user;                            删除user表的所有用户

[root@controller tmp]# mysqld_safe --skip-grant-tables &                     这种方式启动mysql，跳过权限验证，远程，本地，都可以免密登录，生产环境不建议使用

[root@controller tmp]# mysqld_safe --skip-grant-tables --skip-networking &    这种方式启动mysql，允许本地，免密登录

 insert into mysql.user values ('localhost','root',PASSWORD('123456'),
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'',
'',
'',
'',0,0,0,0,'mysql_native_password','','N');