目录
6.配置MySQL,让user从其它节点-node1访问MySQL
1.安装MySQL
sudo apt update
sudo apt install mysql-server -y
sudo systemctl status mysql
2. 运行安全脚本以移除默认的危险选项,并锁定数据库访问:
sudo mysql_secure_installation
按照提示完成以下步骤:
- 设置root用户密码
- 移除匿名用户
- 禁止root远程登录
- 移除测试数据库并重新加载权限表
执行过程需要输入 Y N,根据情况自行选择
root@fred-4:/home/fred-4# sudo mysql_secure_installation
Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
The 'validate_password' component is installed on the server.
The subsequent steps will run with the existing configuration
of the component.
Skipping password set for root as authentication with auth_socket is used by default.
If you would like to use password authentication instead, this can be done with the "ALTER_USER" command.
See https://dev.mysql.com/doc/refman/8.0/en/alter-user.html#alter-user-password-management for more information.
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? (Press y|Y for Yes, any other key for No) : 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? (Press y|Y for Yes, any other key for No) : N
... skipping.
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? (Press y|Y for Yes, any other key for No) : Y
- 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? (Press y|Y for Yes, any other key for No) : Y
Success.
All done!
3.确认数据库可用性(root没有密码,需设置)
root@fred-4:/home/fred-4# mysql
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.02 sec)
mysql> exit
4.设置root密码
密码使用的plugin是 auth_socket,所以可不输入密码就访问数据库,出于安全考虑,需换成mysql_native_password 插件,xxxxxx是密码,请自行定义
root@fred-4:~# mysql
mysql> SELECT user, host, plugin FROM mysql.user WHERE user = 'root';
+------+-----------+-------------+
| user | host | plugin |
+------+-----------+-------------+
| root | localhost | auth_socket |
+------+-----------+-------------+
1 row in set (0.01 sec)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'xxxxxx';
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> exit
Bye
root@fred-4:~#
验证:使用密码登录才能成功
root@fred-4:~# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
root@fred-4:~# mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
root@fred-4:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.37-0ubuntu0.20.04.3 (Ubuntu)Copyright (c) 2000, 2024, Oracle and/or its affiliates.
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>
5.创建MySQL user,赋予特定的权限
mysql> CREATE USER 'fred1'@'%' IDENTIFIED BY 'ffff1122';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql>
mysql> SHOW VARIABLES LIKE 'validate_password%';
+-------------------------------------------------+--------+
| Variable_name | Value |
+-------------------------------------------------+--------+
| validate_password.changed_characters_percentage | 0 |
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 8 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | MEDIUM |
| validate_password.special_char_count | 1 |
+-------------------------------------------------+--------+
8 rows in set (0.01 sec)mysql>
%,使用'%'
表示用户可以从任何主机连接;也可以指定IP等操作
创建用户失败,原因密码不符合安全要求,需要长度大于8、要有数字、要有大写字母
CREATE USER 'fred1'@'%' IDENTIFIED BY 'Fredtest11';
Query OK, 0 rows affected (0.01 sec)
设置符合要求的密码,user创建成功
创建Database, 成功
mysql> CREATE DATABASE addfcuntion;
Query OK, 1 row affected (0.00 sec)mysql>
mysql> CREATE DATABASE delfcuntion;
Query OK, 1 row affected (0.01 sec)mysql>
mysql>
mysql> CREATE DATABASE searchfcuntion;
Query OK, 1 row affected (0.01 sec)mysql>
mysql> CREATE DATABASE modifyfcuntion;
Query OK, 1 row affected (0.01 sec)mysql>
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| addfcuntion |
| delfcuntion |
| information_schema |
| modifyfcuntion |
| mysql |
| performance_schema |
| searchfcuntion |
| sys |
+--------------------+
8 rows in set (0.01 sec)mysql>
赋予user特定的权限,即给fred1 user只有对searchfcuntion数据库仅有选择查看-SELECT和增加-INSERT的权限
mysql> GRANT SELECT,INSERT ON searchfcuntion.* TO 'fred1'@'%';
Query OK, 0 rows affected (0.00 sec)mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)mysql> exit
Bye
6.配置MySQL,让user从其它节点-node1访问MySQL
127.0.0.1是本机访问
root@fred-4:~# sudo netstat -tulnp | grep mysql
tcp 0 0 127.0.0.1:33060 0.0.0.0:* LISTEN 22397/mysqld
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 22397/mysqld
root@fred-4:~#
修改/etc/mysql/my.cnf配置文件,增加以下两行
[mysqld]
bind-address = 0.0.0.0
root@fred-4:~# vi /etc/mysql/my.cnf
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/[mysqld]
bind-address = 0.0.0.0
重启MySQL服务
sudo systemctl restart mysql
检查网络端口信息,0.0.0.0 表示允许所有连接
root@fred-4:~# sudo netstat -tulnp | grep mysql
tcp 0 0 127.0.0.1:33060 0.0.0.0:* LISTEN 25202/mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 25202/mysqld
root@fred-4:~#
如果想限制MySQL服务器只允许来自特定网段(例如192.168.1.0/24)的连接,可结合通过防火墙配置实现:
在Ubuntu上使用
ufw
配置防火墙
sudo ufw allow from 192.168.1.0/24 to any port 3306 sudo ufw reload
MySQL client端访问MySQL server测试:
在fred-1节点,安装MySQL client客户端
root@fred-1:/home/fred-1# apt install mysql-client-core-8.0
root@fred-1:/home/fred-1# mysql -u fred1 -p -h 192.168.1.206 -P 3306
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.37-0ubuntu0.20.04.3 (Ubuntu)
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
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> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
| searchfcuntion |
+--------------------+
3 rows in set (0.01 sec)
mysql>
mysql> use searchfcuntion;
Database changed
mysql>
mysql> SHOW TABLES;
Empty set (0.01 sec)
mysql>
只看到searchfunction的数据库,符合预期!
尝试创建table,失败,无权限,符合预期
mysql> CREATE TABLE users (
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(50) NOT NULL,
-> email VARCHAR(100) NOT NULL,
-> birthdate DATE,
-> is_active BOOLEAN DEFAULT TRUE
-> );
ERROR 1142 (42000): CREATE command denied to user 'fred1'@'192.168.1.200' for table 'users'
需要在root节点创建tables,即执行上述命令;
创建成功后,fred-1 user查看tables信息
mysql> show tables;
+--------------------------+
| Tables_in_searchfcuntion |
+--------------------------+
| users |
+--------------------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT * FROM users;
+----+----------+---------------+------------+-----------+
| id | username | email | birthdate | is_active |
+----+----------+---------------+------------+-----------+
| 1 | ace | ace@gmail.com | 1995-08-01 | 1 |
+----+----------+---------------+------------+-----------+
1 row in set (0.00 sec)
mysql>
Insert数据到tables上成功 ,查看信息也成功,删除则无权限,符合预期!
mysql> INSERT INTO users (username, email) VALUES ('Bob', 'Bob@gmail.com');
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> SELECT * FROM users;
+----+----------+---------------+------------+-----------+
| id | username | email | birthdate | is_active |
+----+----------+---------------+------------+-----------+
| 1 | ace | ace@gmail.com | 1995-08-01 | 1 |
| 3 | Bob | Bob@gmail.com | NULL | 1 |
| 4 | Bob | Bob@gmail.com | NULL | 1 |
+----+----------+---------------+------------+-----------+
3 rows in set (0.00 sec)
mysql>
mysql> DELETE FROM users WHERE id = 4;
ERROR 1142 (42000): DELETE command denied to user 'fred1'@'192.168.1.200' for table 'users'
mysql>
MySQL运维
MySQL运维涉及多个方面的工作,包括安装、配置、备份、恢复、性能优化、安全管理等
性能优化相关
配置文件通常位于/etc/mysql/my.cnf
或/etc/my.cnf
。常见的配置包括:
- 调整缓冲区大小,如
innodb_buffer_pool_size
、query_cache_size
- 设置字符集,如
character-set-server
、collation-server
- 配置日志,如
slow_query_log
、error_log
分析慢查询日志
启用慢查询日志:
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
使用mysqldumpslow
分析慢查询日志:
mysqldumpslow /var/log/mysql/slow.log
调整内存参数
编辑配置文件,调整以下参数:
[mysqld] innodb_buffer_pool_size=1G query_cache_size=64M table_open_cache=2000
索引优化
确保表上有适当的索引以提高查询速度:
CREATE INDEX index_name ON table_name(column_name);
备份相关
使用mysqldump备份
mysqldump -u username -p database_name > backup.sql
恢复备份
mysql -u username -p database_name < backup.sql
物理备份
对于大型数据库,可以使用xtrabackup
或mysqlhotcopy
进行物理备份
安全管理
配置防火墙
确保只有需要访问MySQL的主机可以访问其端口(默认3306)。
在Ubuntu上使用ufw
:
sudo ufw allow from 192.168.1.0/24 to any port 3306
加密连接
在配置文件中启用SSL:
[mysqld] ssl-ca=/path/to/ca-cert.pem ssl-cert=/path/to/server-cert.pem ssl-key=/path/to/server-key.pem
日常维护
检查服务器状态
使用以下命令检查服务器的健康状态:
SHOW STATUS LIKE 'Uptime'; SHOW VARIABLES LIKE 'max_connections'; SHOW PROCESSLIST;
清理日志
定期清理过期的日志文件以释放磁盘空间:
PURGE BINARY LOGS BEFORE 'YYYY-MM-DD HH:MM:SS';
更新MySQL
定期更新MySQL以获取最新的功能和安全补丁:
在Ubuntu上:
sudo apt update sudo apt upgrade mysql-server