MySQL部署后配置
授权root用户登录
#仅本地登录，修改密码用
alter user root@'localhost' identified with mysql_native_password by'*******@666';
创建admin用户设置密码，并使得所有位置都可以访问数据库
create user 'admin'@'%' identified with mysql_native_password by 'your_password';
grant all privileges on *.* to 'admin'@'%';
flush privileges;
#低版本可这样设置root在任意位置登录
grant all privileges on *.* to 'root'@'%' identified with mysql_native_password by 'your_password' with grant option;
#高版本的应该这样设置
sudo mysql_secure_installation运行以下命令以创建一个名为newuser的新用户，并授予其只能从特定IP地址访问MySQL服务器的权限
CREATE USER 'newuser'@'IP_address' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'IP_address';
FLUSH PRIVILEGES;
默认情况下，mysql是运行在127.0.0.1上，此时是无法远程被访问的
root@JumpServer-DB-P01:/opt# netstat -tunlp|grep 3306
tcp 0 0 127.0.0.1:33060 0.0.0.0:* LISTEN 59257/mysqld
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 59257/mysqld
root@JumpServer-DB-P01:/opt#因此需要修改mysqld的配置文件，路径如下
vim /etc/mysql/mysql.conf.d/mysqld.cnf将127.0.0.1改成0.0.0.0 ，保存并重启mysql服务
修改后再次查看mysql是运行在127.0.0.1上
root@JumpServer-DB-P01:~# netstat -tunlp|grep 3306
tcp 0 0 0.0.0.0:33060 0.0.0.0:* LISTEN 79797/mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 79797/mysqld
root@JumpServer-DB-P01:~#指定服务器IP、端口登录数据库
mysql -h 10.0.0.1 -P 3306 -u root -p找到mysql的默认密码在这里：
CentOS:
cat /var/log/mysqld.log
Ubuntu:
cat /etc/mysql/debian.cnf低版本的mysql默认安装后没有密码，可以如下方式设置密码
sudo mysql -u root
#将 "password" 替换为您要设置的密码。请注意，在新的 MySQL 8.0 版本中，密码策略要求密码长度至少为8个字符，并且必须包含数字、小写字母、大写字母和特殊字符。
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';
FLUSH PRIVILEGES;#1、查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| jumpserver |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
#2、使用jumpserver这个数据库
mysql> use jumpserver
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
#3、查看所有的jumpserver库中的表
mysql> show tables;
+---------------------------------------------+
| Tables_in_jumpserver |
+---------------------------------------------+
| accounts_account |
| users_user |
| users_user_groups |
| users_user_user_permissions |
| users_usergroup |
| users_userpasswordhistory |
+---------------------------------------------+
137 rows in set (0.00 sec)
#4、从users_user这个表中计数，算出来22个
mysql> select count(*) from users_user;
+----------+
| count(*) |
+----------+
| 22 |
+----------+
1 row in set (0.01 sec)查看mysql的进程
root@JumpServer-DB-P01:/opt# ps -ef | grep mysql
mysql 59257 1 0 17:36 ? 00:00:01 /usr/sbin/mysqld
root 60629 53830 0 17:39 pts/0 00:00:00 grep --color=auto mysql
root@JumpServer-DB-P01:/opt#查看mysql的端口3306和33060
root@JumpServer-DB-P01:/opt# netstat -tunlp
激活Internet连接 (仅服务器)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 127.0.0.53:53 0.0.0.0:* LISTEN 971/systemd-resolve
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1176/sshd: /usr/sbi
tcp 0 0 127.0.0.1:33060 0.0.0.0:* LISTEN 59257/mysqld
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 59257/mysqld
tcp 0 0 0.0.0.0:6379 0.0.0.0:* LISTEN 31673/redis-server
tcp6 0 0 :::22 :::* LISTEN 1176/sshd: /usr/sbi
tcp6 0 0 ::1:6379 :::* LISTEN 31673/redis-server
udp 0 0 127.0.0.53:53 0.0.0.0:* 971/systemd-resolve
root@JumpServer-DB-P01:/opt#
MySQL数据库基础常用命令8.0版本适用
#查询数据库账户
select user, host from mysql.user;
#删除账户 'zabbix'
drop user 'zabbix'@'%';
#查询数据库
show databases;
#删除数据库zabbix;
drop database zabbix;
#修改账户密码
alter user 'zabbix'@'%' identified by 'Qqqq1234.com';
分割线
从docker环境中的mysql里备份数据库
用docker ps命令定位容器名称叫jms_mysql
history
606 docker exec -it jms_mysql bash
607 docker ps
608 docker cp jms_mysql:/all_db_with_data.sql /root/
root@jumpserver-app-p01:/root#docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
3081f875d979 jumpserver/core:v3.0.3 "./entrypoint.sh sta…" 9 days ago Up 9 days (healthy) 8080/tcp jms_core
c9f813565a08 jumpserver/mariadb:10.6 "docker-entrypoint.s…" 9 days ago Up 9 days (healthy) 3306/tcp jms_mysql
207f878b9a66 jumpserver/redis:6.2 "docker-entrypoint.s…" 9 days ago Up 9 days (healthy) 6379/tcp jms_redis
root@jumpserver-app-p01:/root#进入到jms_mysql容器中
root@jumpserver-app-p01:/root#docker exec -it jms_mysql bash
root@c9f813565a08:/#导出所有的数据库（此方法数据库版本必须一致）
root@c9f813565a08:/# mysqldump -h127.0.0.1 -P3306 -uroot -pNDIwNURCOTYtMDYzQS01Njg0LT --all-databases > all_db_with_data.sql
root@c9f813565a08:/# ls
all_db_with_data.sql bin boot dev docker-entrypoint-initdb.d etc home lib lib32 lib64 libx32 media mnt opt proc root run sbin srv sys tmp usr var
root@c9f813565a08:/# exit#存到root目录下
root@jumpserver-app-p01:/opt/jumpserver/config#docker cp jms_mysql:/all_db_with_data.sql /root/
#进入root目录
root@jumpserver-app-p01:/opt/jumpserver/config#cd
#查看文件
root@jumpserver-app-p01:/root#ls
all_db_with_data.sql anaconda-ks.cfg Desktop Documents Downloads initial-setup-ks.cfg jp.sql Music Pictures Public Templates Videos
#传输文件到本地
root@jumpserver-app-p01:/root#sz all_db_with_data.sql导出指定数据库（以jumpserver数据库为例）
登录数据库查看其中包括的jumpserver数据库名称后退出
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| jumpserver |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.000 sec)
MariaDB [(none)]> exit用mysqldump导出jumpserver数据库为sql语句
root@c9f813565a08:/# mysqldump -B jumpserver -h127.0.0.1 -uroot -pNDIwNURCOTYtMDYzQS01Njg0LT > jumpserver.sql
root@c9f813565a08:/# ls
jumpserver.sql boot docker-entrypoint-initdb.d home lib lib64 media opt root sbin sys usr
bin dev etc lib32 libx32 mnt proc run srv tmp var
root@c9f813565a08:/# exit
exit退出容器，在容器内的jumpserver.sql语句cp到容器外的root目录下，最后sz传输到本地
root@jumpserver-app-p01:/root#docker cp jms_mysql:/jumpserver.sql /root/
root@jumpserver-app-p01:/root#ls
all_db_with_data.sql anaconda-ks.cfg Desktop Documents Downloads initial-setup-ks.cfg jp.sql jumpserver.sql Music Pictures Public Templates Videos
root@jumpserver-app-p01:/root#sz jumpserver.sql进入到目标数据库中，把本地文件上传输到目标服务器的/opt路径下。通过拖拽或者sftp传输均可
root@JumpServer-DB-P01:~# cd /opt
root@JumpServer-DB-P01:/opt# ls
jumpserver.sql redis redis-6.2.10 redis-6.2.10.tar.gz
root@JumpServer-DB-P01:/opt#在此目录下登录mysql，先创建jumpserver数据库，再执行source 导入表
root@jumpserver-app-t01:#mysql -uroot -p
Enter password:
mysql> create database jumpserver default charset 'utf8' collate 'utf8_bin';
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> 使用jumpserver数据库，然后执行source命令导入表
mysql> use jumpserver;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source /opt/jumpserver.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected, 2 warnings (0.05 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 49 rows affected (0.00 sec)
Records: 49 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
mysql> 检查结果：导入137条完成。
mysql> show tables;
+---------------------------------------------+
| Tables_in_jumpserver |
+---------------------------------------------+
| accounts_account |
| accounts_accountbackupautomation |
| users_user_groups |
| users_user_user_permissions |
| users_usergroup |
| users_userpasswordhistory |
+---------------------------------------------+
137 rows in set (0.00 sec)
mysql>
