mysql shell
MySQL Shell 8.0 is highly recommended for use with MySQL Server 8.0 and 5.7. Please upgrade to MySQL Shell 8.0. If you have not yet installed MySQL Shell, download it from the download site.
https://cdn.mysql.com//Downloads/MySQL-Shell/mysql-shell-8.0.29-linux-glibc2.12-x86-64bit.tar.gz
./mysqlsh --help
Usage examples:
$ mysqlsh root@localhost/schema
$ mysqlsh mysqlx://root@some.server:3307/world_x
$ mysqlsh --uri root@localhost --py -f sample.py sample param
$ mysqlsh root@targethost:33070 -s world_x -f sample.js
$ mysqlsh -- util check-for-server-upgrade root@localhost --output-format=JSON
$ mysqlsh mysqlx://user@host/db --import ~/products.json shop
dump/load schema
mysqlsh --uri root@localhost:3306/schema
util.dumpTables("schema", [], "/root/schema.dump", {all: true})
# 在恢复的设备上
util.loadDump("/tmp/dump", {schema: "test2"})
- https://www.sqlshack.com/how-to-backup-and-restore-mysql-databases-using-the-mysqldump-command/
- https://lefred.be/content/how-to-copy-a-schema-using-mysql-shell-dump-load-utility/
mysql最常用最基础的命令
0. 重置root密码
安装之后重置root密码,
mysql版本8.0已经取消了password字段和password()函数,而且不在支持SET PASSWORD … =PASSWORD(‘auth_string’)语法:
在5.7之前修改密码:(使用update修改user表)
update mysql.user set authentication_string=password('123456') where host='localhost' and user='root';
在5.7修改密码:(因为废除了password字段,需要使用authentication_string)
update mysql.user set password=password('123456') where host='localhost' and user='root';
而在8.0已经不能用possword函数和set…语句,只能用:
alter user 'root'@'localhost' identified with mysql_native_password by '123456';
参考:
https://blog.csdn.net/ZZQHELLO2018/article/details/96720362
https://blog.csdn.net/qq_24654501/article/details/106101633
1、以管理员身份登录mysql
mysql -u root -p
注意-p后面如果接密码,则密码与-p之间没有空格(这奇葩语法!)
2、选择mysql数据库
进入mysql命令行之后,
mysql>use mysql;
3、创建用户并设定密码
mysql>create user 'testuser'@'localhost' identified by 'test password';
注意在使用非本地的机器访问时,需要@的是mysql客户端的IP:
grant all privileges on xxl_job.* to xxl_job@192.168.85.1 identified by 'xxl_job';
4、使操作生效
mysql>flush privileges;
5、为用户创建数据库
mysql>create database testdb;
6、为用户赋予操作数据库testdb的所有权限
mysql>grant all privileges on testdb.* to test@localhost identified by '1234';
高版本去掉
identified by '1234';
7、使操作生效
mysql>flush privileges
8、用新用户登录
mysql -u test -p
远程指定端口的命令:
mysql -h 10.a.b.c -P8306 -u test -ptest
原文链接:
http://my.oschina.net/u/165872/blog/78863
重置密码
mysql>update user set password=PASSWORD("mynewpassword") where User='root' and Host='localhost';
删除用户
mysql>delete from user where user='test2' and host='localhost';
mysql>flush privileges;
监听所有地址
sudo vi /etc/my.cnf
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
# 增加一行
bind-address=0.0.0.0
# 重启
sudo service mysqld restart
执行外部sql文件
在mysql命令行source 这个sql文件就好了。
原文链接:
http://www.ghostchina.com/how-to-reset-mysqls-root-password/
debug
使用mysql 8之后,发现报错:
org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed
参考:
https://kalasearch.cn/community/tutorials/ubuntu-20-04-install-mysql/
修改密码类型:
ALTER USER 'java_sec_code'@'192.168.96.1' IDENTIFIED WITH mysql_native_password BY 'java_sec_code';
讲caching_sha2_password改成了mysql_native_password类型。
Establishing SSL connection without server’s identity verification is not recommended
设置数据库连接字符串:
useSSL=false
在拿到的shell上直接执行sql命令,mysql 客户端:
https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-connection-using-parameters.html
查看历史命令
sudo cat /root/.mysql_history|sed "s/\\\040/ /g"
参考:
- https://blog.csdn.net/qq_28867949/article/details/79465682
- https://stackoverflow.com/questions/34189756/warning-about-ssl-connection-when-connecting-to-mysql-database
- https://stackoverflow.com/questions/7818031/sql-command-to-display-history-of-queries
- MySQL注入 利用系统读、写文件