更改root密码
- 默认mysql是没有密码的
- mysql设置初始密码
mysqladmin -uroot password 'baby123'
- 修改密码:
mysqladmin -uroot -p'baby123' password 'baby321'
还有其他修改密码的命令如,grant, alter, SET,修改密码要修改localhost的密码,修改后127.0.0.1的密码跟着变;
修改127.0.0.1的密码,不生效;
- 忘记密码的情况下修改密码
vi /etc/my.cnf #增加 skip-grant; 保存;
/etc/init.d/mysqld restart
mysql -uroot #可以不用密码登陆mysql;
登陆进mariadb后
use mysql; #切换到mysql库;
desc user; #查看user表的所有字段;
update user set authentication_string=password("aming-linux") where user='root';
#修改了所有root的密码;
exit;
vi /etc/my.cnf #注释 skip-grant; 保存;
/etc/init.d/mysqld restart
mysql -uroot -p #用新密码登陆;
mysql在5.7.36版本之后把密码字段存到了authentication_string字段里,在之前版本存在password字段里, 旧版本修改密码命令如下:
update user set password=password("aming-linux") where user='root';
连接MySQL
mysql -uroot -p123456 #相当于使用socket连接;
mysql -uroot -p123456 -h127.0.0.1 -P3306 #指定IP和port连接;
mysql -uroot -p123456 -S/tmp/mysql.sock #只适合在本机的情况;
mysql -uroot -p123456 -e “show databases” #在shell界面显示数据库查询结果;
MySQL常用命令
查询库 show databases;
切换库 use mysql;
查看库里的表 show tables;
查看表里的字段 desc tb_name;
查看建表语句 show create table tb_name\G; #\G坚排显示;
select * from user\G;
查看当前用户 select user();
MariaDB [(none)]> grant all on *.* to 'tany'@'192.168.87.133' identified by 'password';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> exit
Bye
[root@draft 112503]# mysql -utany -ppassword -h192.168.87.133
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 26
Server version: 10.2.27-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select user();
+-------------------+
| user() |
+-------------------+
| tany@www.tany.com |
+-------------------+
1 row in set (0.00 sec)
查看当前使用的数据库 select databsase();
创建库 create database db1;
创建表
use db1;
create table t1(`id` int(4), `name` char(40));
drop table t1; #删除表;
create table t1(`id` int(4), `name` char(40)) ENGINE=InnoDB DEFAULT CHARSET=utf8; #修改字符集;
查看当前数据库版本 select version();
查看数据库状态 show status;
查看参数 show variables;
查看max_connect开头的参数 show variables like ‘max_connect%’;
查看slow开头的参数 show variables like ‘slow%’;
修改参数 set global max_connect_errors=1000; #在内存里生效,长期有效要修改my.cnf;
查看队列 show processlist; show full processlist;
mysql容易混淆的信息
- grant user@127.0.0.1 是授权user从本机127.0.0.1访问数据库,同样代表访问目标是127.0.0.1;
-
grant user@192.168.87.141,就是授权user从141的机器上访问数据库;
-
mysql -uuser -h192.168.87.133 是以user帐号访问133机器上的mysql,此时对于目标数据库来源是 本机IP(假设是192.168.87.141),要使用上面的语句在133数据库上授权,grant user@192.168.87.141; 这样才对应得上;
-
grant user@localhost 是授权user从本机的socket访问数据库,同样代表访问目标是socket;另外socket只能本机使用;
- grant user1@127.0.0.1 登陆是不能使用mysql -uuser1 直接登陆,因为这样登陆目的地是socket, 入口也要是socket, 但是user1没有被授权socket的登陆权限;
- grant user2@localhost 登陆是不能使用mysql -uuser2 -h127.0.0.1, 因为这样登陆目的地是IP, 入口也要是IP,但是user2没有被授权IP的登陆权限;
参考信息:https://www.jb51.net/article/141702.htm
MySQL创建用户以及授权
grant all on *.* to 'user1' identified by 'passwd'; #授权user1的权限;
grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd'; #授权user2有某些权限;
grant all on db1.* to 'user3'@'%' identified by 'passwd'; #user3不限制登陆IP;
show grants; #查看当前用户的权限;
show grants for user2@192.168.133.1; #显示一个用户在某个登陆IP的权限,可用于复制用户权限;
常用MySQL语句
select count(*) from mysql.user; #查询user表的行数;
select * from mysql.db; #查询表的所有内容;尽量少做全匹配的操作,耗费资源;
select db from mysql.db; #显示db字段;
select db,user from mysql.db; #显示两个字段;
select * from mysql.db where host like '192.168.%'; #模糊查找;
insert into db1.t1 values (1, 'abc'); #插入数据信息,要根据表的定义输入数据;
update db1.t1 set name='aaa' where id=1; #修改表信息;
delete from db1.t1 where id=2; #删除表信息;
truncate table db1.t1; #清空一个表;
drop table db1.t1; #清除一个表;
drop database db1; #清除一个数据库;
MySQL数据库备份恢复
备份库 mysqldump -uroot -p123456 mysql > /tmp/mysql.sql
恢复库 mysql -uroot -p123456 mysql < /tmp/mysql.sql
直接进入一个库 mysql -uroot -p123456 mysql
备份表 mysqldump -uroot -p123456 mysql user > /tmp/user.sql
恢复表 mysql -uroot -p123456 mysql < /tmp/user.sql
备份所有库 mysqldump -uroot -p -A >/tmp/123.sql
只备份表结构 mysqldump -uroot -p123456 -d mysql > /tmp/mysql.sql
试用记录:
mysqldump -S /tmp/mysql.sock -uroot -pFriday28 mysql > /tmp/mysqlbak.sql #备份mysql库;
mysql -uroot -pFriday28 -S /tmp/mysql.sock -e "create database mysql2" #创建新库;
mysql -S /tmp/mysql.sock -uroot -pFriday28 mysql2 < /tmp/mysqlbak.sql #把mysql数据恢复到mysql2;
mysql -S /tmp/mysql.sock -uroot -pFriday28 mysql2 #进入mysql2库;查看数据;
mysqldump -uroot -pFriday28 -S /tmp/mysql.sock mysql user > /tmp/user.sql #备份user表;
less /tmp/user.sql #查看备份文件;
less /tmp/mysqlbak.sql #查看备份文件;
mysql -uroot -pFriday28 -S /tmp/mysql.sock mysql2 < /tmp/user.sql
#把表恢复,不用指定表,会把里边的user表删除,恢复user表;
mysqldump -uroot -pFriday28 -S /tmp/mysql.sock -A > /tmp/mysql_all.sql #备份所有库;
less /tmp/mysql_all.sql #查看备份文件;
mysqldump -uroot -pFriday28 -S /tmp/mysql.sock mysql2 -d > /tmp/mysql2d.sql #备份表结构;
less /tmp/mysql2d.sql #查看备份文件,里面没有数据,所有没有INSERT语句;
使用xtrabackup备份mysql
备份逻辑
- 需要全量备份数据库,之后备份可以根据前一个备份文件作增量备份;
- 需要恢复到某一个时间的备份文件时,要将该时间点及之前的增量备份文件合并到全量文件里,然后再实施恢复;
- 恢复时,需要将全量文件备份,因为合并出问题的情况下,全量文件被污染,会导致问题;备份了全量文件可重新操作;实际上增量文件也不能使用第二次,也需要备份;
- xtrabackup操作成功都会显示"completed OK!"
备份操作
- 安装xtrabackup
#rpm -ivh http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
#yum install -y percona-xtrabackup-24.x86_64
错误信息:
warning: /var/cache/yum/x86_64/7/percona-release-x86_64/packages/percona-xtrabackup-24-2.4.15-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
从 file:///etc/pki/rpm-gpg/RPM-GPG-KEY-Percona 检索密钥
源 "Percona-Release YUM repository - x86_64" 的 GPG 密钥已安装,但是不适用于此软件包。请检查源的公钥 URL 是否配置正确。
失败的软件包是:percona-xtrabackup-24-2.4.15-1.el7.x86_64
GPG 密钥配置为:file:///etc/pki/rpm-gpg/RPM-GPG-KEY-Percona
- 修改repo文件
#cd /etc/yum.repos.d/
#vi percona-release.repo #把gpg都改为0;
- 再安装xtrabackup
#yum install -y percona-xtrabackup-24.x86_64
- 新建备份用户,权限如下说明所示
#mysql -uroot -S /tmp/mysql.sock #进入mysql 新建backupuser;
grant reload,lock tables,replication client, process, super on *.* to 'backupuser'@'localhost' identified by 'tany';
flush privileges;
- 新建备份保存路径
#mkdir /data/backup #新建backup保存路径;
- 执行全量备份,实验做了一次全量备份和二次增量备份;
#innobackupex --defaults-file=/etc/my.cnf --user=backupuser --password='tany' -S /tmp/mysql.sock /data/backup #需要指定配置文件,备份用户密码,socket,备份路径,会在路径下生成以时间命名的备份文件;
- 做增量备份
#mysql -uroot #修改数据库,容易分辨是否备份成功;
#ll /data/backup/ #查看备份文件名称;
#innobackupex --defaults-file=/etc/my.cnf --user=backupuser --password='tany' -S /tmp/mysql.sock --incremental /data/backup --incremental-basedir=/data/backup/2019-10-27_18-45-00 #增量备份,--incremental说明是增量备份,并需要指定基于哪一个文件做增量备份;
#ll /data/backup/ #查看备份文件;
#mysql -uroot #修改数据库内容;
#innobackupex --defaults-file=/etc/my.cnf --user=backupuser --password='tany' -S /tmp/mysql.sock --incremental /data/backup --incremental-basedir=/data/backup/2019-10-27_18-53-26 #第二次增量备份,需要指定基于哪一个文件做增量备份,这里指定上一个增量备份的文件;ll
#du -sh /data/backup/* #查看文件大小;
15M /data/backup/2019-10-27_18-45-00
10M /data/backup/2019-10-27_18-53-26
10M /data/backup/2019-10-27_18-56-28
- 准备恢复数据,合并数据后,全量备份会越来越大,最终比原目录略大(可能是日志文件)
#service mysqld stop #停止服务才能恢复;
#mv /data/mysql /data/mysql.bak #移除数据,试验使用备份恢复数据;
#cp -r /data/backup/2019-10-27_18-45-00 /data/backup/2019-10-27_18-45-00.bak #备份全量备份文件;
#innobackupex --apply-log --redo-only /data/backup/2019-10-27_18-45-00/ #恢复准备的第一步指定全量备份文件,--redo-only使用增量恢复时需要这个参数,--apply-log指使用备份的log和生成新的log;目录后需要/;
#ll /data/backup/ #查看文件名称;
#innobackupex --apply-log --redo-only /data/backup/2019-10-27_18-45-00/ --incremental-dir=/data/backup/2019-10-27_18-53-26/ #恢复准备的第二步,指定增量备份文件(第一个增量文件);
#ll /data/backup/ #查看文件名称;
#innobackupex --apply-log /data/backup/2019-10-27_18-45-00/ --incremental-dir=/data/backup/2019-10-27_18-56-28/ #恢复准备的第三步,指定第二个备份文件,也是最后一个备份文件,不需要--redo-only参数;如果还有备份文件,直到最后一个前都需要--redo-only;
#innobackupex --apply-log /data/backup/2019-10-27_18-45-00/ #恢复准备的第四步,rollback全量备份;
#ll /data/backup/2019-10-27_18-45-00/ #可以看到文件已经合并到全量备份文件里;
- 恢复数据
#innobackupex --copy-back /data/backup/2019-10-27_18-45-00/ #恢复数据;
#service mysql start #启动mysqld,发生错误;
#vi /var/log/mysql.log #查看日志;
The system tablespace must be writable! #权限问题;
#chown -R mysql:mysql /data/mysql #修改权限;
#service mysqld start #启动mysqld;
#mysql -uroot #查看数据库是否恢复;