13.1 设置更改root密码
13.2 连接mysql
13.3 mysql常用命令
13.4 mysql用户管理
13.5 常用sql语句
13.6 mysql数据库备份恢复
13.7拓展:innobackex工具备份mysql数据
13.1 设置更改root密码
• /usr/local/mysql/bin/mysql -uroot
• #更改环境变量PATH,增加mysql绝对路径,就可直接mysql -uroot
export PATH=$PATH:/usr/local/mysql/bin/
#让该命令永久生效,将上面添加PATH的命令放到/etc/prefile文件里面,然后source /etc/profile,让配置立即生效
mysql -uroot -p123456 #-p输入密码 ,密码正确进入mysql
#密码最好加上单引号,因为如果密码含有特殊字符便识别不了
mysqladmin -uroot password '123456'
#设置mysql的root用户密码
mysqladmin -uroot -p '123456' password '1234' #更改root密码
密码重置(不知道密码)
1、编辑mysql配置文件
vi /etc/my.cnf
#增加一行内容 skip-grant
#skip-grant 表示忽略授权,即不用用户名密码可以直接登录
2、 重启mysql服务,让前面的配置生效
/etc/init.d/mysqld restart
3、登录mysql,更改mysql库的用户密码表,达到重新设置密码的目的
mysql -uroot #登录mysql
use mysql; #切换到mysql库
update user set password=password('123456') where user='root'; #修改root密码为123456
4、编辑mysql配置文件,将增加的skip-grant删掉,然后重启mysqld服务
13.2 连接mysql
连接mysql的命令
mysql -uroot -p123456
#连接本机mysql
mysql -uroot -p123456 -h127.0.0.1 -P3306
#连接远程mysql,需要-h输入ip,还有-P指定端口
mysql -uroot -p123456 -S/tmp/mysql.sock
#通过-S指定socket文件进行链接(只适用于本机)
mysql -uroot -p123456 -e “show databases”
#-e选项,不进入mysql对数据库的内容进行操作(一般使用在shell脚本里面)
#不进入mysql将所有的数据库列出来
13.3 mysql常用命令
1、查询库 show databases;
2、切换库 use mysql;
3、 查看库里的表 show tables;
4、查看表里的字段 desc tb_name;
5、 查看建表语句
show create table tb_name\G;
#\G的作用是使数据排列整齐
6、 查看当前用户 select user();
#会将ip反解析成主机名,跟在用户名后面
7、查看当前使用的数据库 select databsase();
8、创建库 create database db1;
9、创建表
use db1;
create table t1(`id` int(4), `name` char(40));
10、查看查看创建表的语句
show create tables t1\G;
#mysql默认的字符集是latin1,可以修改,只需要在创建表的语句指定字符集,比如: create table t1(`id` int(4), `name` char(40)) ENGINE=InnoDB DEFAULT CHARSET=utf8
#在不想执行的语句开头加上#号,语句便不执行
11、删除表 drop table t1;
12、查看当前数据库版本 select version();
13、 查看数据库状态 show status;
14、查看各参数
show variables; #查看所有参数
show variables like 'max_connect%';
#查看指定参数包含max_connect,%表示通配
15、修改参数
set global max_connect_errors=1000;
16、查看队列
show processlist; #最后一列不完整
show full processlist; #加上full,最后一列完整列出
#使用mysql数据库后会在root目录下生成.mysql_history的文件,可以查看mysql命令历史
13.4 mysql用户管理
#grant语句是不会记录到命令历史里面的,因为不安全
grant语句说明:
#grant指定权限,@后面指定用户只能通过来源ip进行登录或者localhost,identified by指定用户密码
#@指定localhost时,对应的是socket文件
grant all on *.* to 'user1'@'127.0.0.1 identified by 'passwd';
#创建mysql用户user1,设定密码为passwd,授于所有权限,限定只能通过127.0.0.1进行登录
grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.233.1' identified by 'passwd';
#创建用户user2,密码为passwd,授予允许在db1库查,改,增的权限,限定只能192.168.233.1进行登录
grant all on db1.* to 'user3'@'%' identified by 'passwd'
#%表示通配,这里指的是所有的ip
#创建user3,密码为passwd,授于所有权限,允许所有ip进行登录
show grants; #查看当前用户的权限语句
show grants for user2@192.168.233.1;
#查看指定用户的授权语句
退出mysql的三种方式:
1、quit
2、exit
3、CTRL+D
13.5 常用sql语句
1、select查看语句
select count(*) from mysql.user;
#count指的是行数,库和表之间用"."点进行隔开
#查看mysql库中user表的行数
当使用select * from的操作时,(建议少用这个*操作)
mysql库里面所有的表默认使用的引擎是MyISAM,该引擎会自动统计表的行数,所以就算用*操作时查询会快一点;
但是其他库里面所有的表默认使用的引擎是InnoDB,用*操作时会很慢,很消耗内存。
select * from mysql.db;
#这里的*指的是所有内容
#查看mysql库的db表的所有内容
select db from mysql.db;
#查看mysql库的db表的db字段
select db,user from mysql.db;
#查看mysql库的db表的db字段和user字段
#多个字段查询用逗号隔开即可","
select * from mysql.db where host like '192.168.%';
#模糊查询,查看mysql库的db表中与192.168.%有关的内容
2、insert插入语句
insert into db1.t1 values (1, 'abc');
#在db1库的t1表内插入数据(1,'abc')
desc db1.t1;
insert into db1.t1 values (1, 'abc');
select * from db1.t1;
3、update更改语句
update db1.t1 set name='aaa' where id=1;
#更改db1库t1表内id=1的行的name字段为aaa
#where前面的条件是查找,后面的条件是替换
4、delete删除表内容
delete from db1.t1 where id=2;
#删除db1库t1表内id=2的行
5、truncate清空表内容
truncate table db1.t1;
#将db1库t1表的内容清空
6、drop删除表或库
drop table db1.t1;
#删除db1.t1表
drop database db1;
#删除db1库
delete、truncate、drop的区别
1、delete、truncate只是删除表的内容,表结构没有变化
2、drop直接删除表,表结构发生变化
13.6 mysql数据库备份恢复
#备份用mysqldump,恢复用mysql
1、备份库
语法:
mysqldump -uroot -p密码 备份的库名
mysqldump -uroot -p123456 mysql > /tmp/mysql.sql
#将备份的mysql库重定向到/tmp/mysql.sql文件内
2、恢复库
mysql -uroot -p123456 mysql < /tmp/mysql.sql
演示:创建一个新的库mysql2,然后将备份的mysql库数据传到mysql2库
mysql -uroot -p123456 -e "create database mysql2" #创建mysql2库
mysql -uroot -p123456 mysql < /tmp/mysql.sql
将备份的/tmp/mysql.sql 文件反向重定向到mysql2库
3、备份表
mysqldump -uroot -p123456 mysql user > /tmp/user.sql文件
#备份mysql库的user表到/tmp/user.sql
4、恢复表
mysql -uroot -p123456 mysql < /tmp/user.sql
#要恢复表只需要写表所在的库名即可
5、备份所有库-A
mysqldump -uroot -p123456 -A >/tmp/123.sql
#-A表示所有all的意思
#备份所以库到/tmp/123.sql文件
6、只备份表结构-d
mysqldump -uroot -p123456 -d mysql > /tmp/mysql.sql
#备份mysql库中的表结构
13.7拓展:innobackex工具备份mysql数据
xtrbackup只能用于备份innodb引擎的数据库,而innobackex 既可以备份innodb引擎的数据库,也可以备份myisam引擎的数据库。备份时也可分为全量备份和增量备份
一、安装innobackex
1.1、安装yum拓展源percona-release
rpm -ivh http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
1.2、安装percona-xtrabackup
yum install percona-xtrabackup
二、全量备份mysql
2.1、创建并授权备份用户
我们可以直接授权all权限,但是不符合安全原则
mysql -uroot -pallen
> grant reload,lock tables,replication client on *.* to 'backupuser'@'localhost' identified by 'allen';
> flush privileges;
权限为reload,lock tables,replication client。
2.2、创建备份保存目录
mkdir /data/backup
2.3、备份mysql
innobackupex --defaults-file=/etc/my.cnf --user=backupuser --password='allen' -S /tmp/mysql.sock /data/backup
#–defaults-file=/etc/my.cnf指定配置文件位置是为了获得datadir位置
#备份完成后,会在指定的保存目录中生成一个时间戳目录,该时间戳目录名称也是恢复时的apply-log。
备份时出现的错误:
三、全量备份恢复
模拟故障,此时mysql的数据损毁。而之前我们已经全量备份。
3.1、停止mysql服务
/etc/init.d/mysqld stop
ps aux |grep "mysqld"
#不允许mysql进程存在
3.2、删除mysql原有数据
mv /data/mysql /data/mysql.bak
mkdir /data/mysql
3.3、恢复mysql
1)首先,需要初始化(初始化的叫法不够严谨)
innobackupex --use-memory=512M --apply-log /备份的时间戳目录/
# innobackupex --use-memory=512M --apply-log /data/backup/2017-08-23_10-24-06/
#–use-memory=512M指定备份时使用的内存为512M,注意单位。默认为字节
2)初始化完成后,进行恢复
innobackupex --defaults-file=/etc/my.cnf --copy-back /备份的时间戳目录/
#innobackupex --defaults-file=/etc/my.cnf --copy-back /data/backup/2017-08-23_10-24-06/
#使用–copy-back参数恢复
3)设置权限
chown -R mysql:mysql /data/mysql
四、增量备份
之前我们已经进行了全量备份,经过操作,一段时间后重新全量备份的话,需要耗费的资源较多,这时我们就可以使用增量备份了。
本次实验将执行一次全量备份,两次增量备份
4.1、进行一次全量备份
增量备份是基于全量备份的,所以在增量备份操作之前我们需要先进行全量备份
# innobackupex --defaults-file=/etc/my.cnf --user=backuser --password='allen' -S /tmp/mysql.sock /data/backup
#/data/backup/2017-08-24_23-05-09/就是我们全量备份的时间戳目录
4.2、进行增量备份
4.2.1、进行数据库操作
#创建测试库test1_backup
mysql -uroot -pallen -e 'create database test1_backup;'
#导入数据
mysql -uroot -pallen test1_backup < /tmp/1.sql
#模拟数据库数据发生改变,进行增量备份
4.2.2、进行第一次增量备份
innobackupex --defaults-file=/etc/my.cnf --user=backuser --password='allen' -S /tmp/mysql.sock --incremental /data/backup --incremental-basedir=/data/backup/全量备份时间戳目录/
#–incremental表示增量备份,–incremental-basedir指定全量备份时间戳目录,因为本次增量备份是基于全量备份。
#本次增量备份的时间戳目录为/data/backup/2017-08-24_23-15-19
4.2.3、进行数据库操作
#创建测试库test2_backup
mysql -uroot -pallen -e 'create database test2_backup;'
#导入数据
mysql -uroot -pallen test2_backup < /tmp/1.sql
4.2.4、进行第二次增量备份
innobackupex --defaults-file=/etc/my.cnf --user=backuser --password='allen' -S /tmp/mysql.sock --incremental /data/backup --incremental-basedir=/data/backup/第一次增量备份时间戳目录/
#–incremental-basedir指定第一次增量备份时间戳目录,因为本次增量备份是基于第一次增量备份。
#本次增量备份的时间戳目录为/data/backup/2017-08-24_23-21-16/
经过以上操作,我们的增量备份就完成了
4.3、增量备份指定的INCREMENTAL-BASEDIR如何确定
在上面的实验中,我们进行增量操作时。第一次增量的incremental-basedir是全量备份时间戳目录,第二次增量的incremental-basedir是第一次增量备份的时间戳目录,这是为什么呢?
在每个备份的时间戳目录下面都有一个文件,里面记录了本次备份的检查点。
#from_lsn为起始检查点,to_lsn为终止检查点
#全量备份的from_lsn为0,to_lsn为第一次增量备份的from_lsn,而第一次增量备份的to_lsn为第二次增量备份的from_lsn。刚好组成0-1863060。
#如果上面的检查点不是依次连续的,那么在后面的增量恢复时容易出现错误。
如果每一次都是使用全量备份时间戳目录作为incremental-basedir,那么会出现以下情况
#第二次增量备份的to_lsn会覆盖第一次增量备份的to_lsn。
#如果是这样,那么我们在恢复时只需要初始化全量备份和第二次增量备份的时间戳目录
五、增量备份恢复
模拟故障,此时mysql的数据损毁。而之前我们已经进行全量和增量备份。
5.1、停止mysql服务
/etc/init.d/mysqld stop
ps aux |grep "mysqld"
#不允许mysql进程存在
5.2、删除mysql原有数据
mv /data/mysql /data/mysql.bak
mkdir /data/mysql
5.3、增量备份合并步骤
1、先初始化全量
innobackupex --apply-log --redo-only /data/backup/全量备份目录
2、初始化第一次增量
innobackupex --apply-log --redo-only /data/backup/全量备份目录 --incremental-dir=/data/backup/第一次增量备份目录
3、innobackupex --apply-log /data/backup/全量备份目录 --incremental-dir=/data/backup/第二次增量备份目录
4、再初始化一次全量
innobackupex --apply-log /data/backup/全量备份目录
#–redo-only参数是增量备份必须的参数,第一步和最后一步初始化都是初始化全量备份,但是最后一步不需要–redo-only参数。(因为全部数据都在最后一步的全量目录中)
#除了第一步和最后一步,其余步骤都是初始化增量备份,最后一次增量备份也不需要–redo-only参数
#
使用–use-memory可以设置执行时使用的内存大小
5.4、增量备份恢复
进行初始化,执行恢复时与全量备份恢复一样,都是指定全量备份目录
innobackupex --copy-back /bakdir/全量备份目录
#为了避免权限问题,导致mysql无法启动
chown -R mysql:mysql /data/mysql
所以,增量备份恢复在本次实验的步骤如下。
#停止mysql服务
/etc/init.d/mysqld stop
#模拟数据库损毁
mv /data/mysql /data/mysql.bak
mkdir /data/mysql
chown mysql:mysql /data/mysql
#初始化全局
innobackupex --apply-log --redo-only /data/backup/2017-08-24_23-05-09
#初始化第一次增量
innobackupex --apply-log /data/backup/2017-08-24_23-05-09 --incremental-dir=/data/backup/2017-08-24_23-21-16
#初始化第二次增量,这是最后一次增量,所以不需要--redo-only参数
innobackupex --apply-log /data/backup/2017-08-24_23-05-09 --incremental-dir=/data/backup/2017-08-24_23-21-16
#再初始化全量,不使用--redo-only
innobackupex --apply-log /data/backup/2017-08-24_23-05-09
#恢复mysql
innobackupex --copy-back /data/backup/2017-08-24_23-05-09
#设置权限,避免启动mysql出错
chown -R mysql:mysql /data/mysql
5.5、查看是否成功
#启动test1_backup和test2_backup是做完全量备份才创建的测试库,说明恢复成功
ps:在进行增量备份时,每一次的–incremental-basedir都指定全量备份时间戳。那么在恢复时,只需要初始化全量备份时间戳目录和最后一次增量备份的时间戳目录 。
1、先初始化全量
innobackupex --apply-log --redo-only /data/backup/全量备份目录
2、初始化最后一次增量
innobackupex --apply-log /data/backup/全量备份目录 --incremental-dir=/data/backup/最后一次增量备份目录
3、再初始化一次全量,不需要--redo-only
innobackupex --apply-log /data/backup/全量备份目录
4、恢复
innobackupex --copy-back /data/backup/全量备份目录