数据库管理

数据库管理

安装mariadb数据库

yum -y groupinstall mariadb

 

修改配置文件

vim /etc/my.cnf

    # innodb 支持数据回滚
    innodb_file_per_talbe = 1

    # 开启日志功能
    log-bin = /var/lib/mysql/bin-log

    :wq

 

启动服务，并添加开机启动脚本

systemctl restart mariadb
systemctl enable mariadb

 

安全初始化数据库

mysql_secure_installation

Enter current password for root (enter for none): <回车>

Set root password? [Y/n] Y

New password: 

Re-enter new password:

Remove anonymous users? [Y/n] Y

Disallow root login remotely? [Y/n]

Remove test database and access to it? [Y/n] Y

Reload privilege tables now? [Y/n] Y

 

数据库选项和参数

# 指定用户名

　　mysql -u

# 指定密码

　　mysql -p

# 执行一条SQL语句然后退出

　　mysql -e '...'

# 指定连接主机

　　mysql -h x.x.x.x

# 指定端口

　　mysql -P(p为指定密码选项时) <0~65535>

# 结束符

　　/d

# 整行显示输出

　　/G

 

忘记数据库密码

# 1. 修改配置文件
    vim /etc/my.cnf

        # 跳过授权表认证
        skip-grant-tables = 1

        # 关闭网络连接数据库 
        skip-networking = 1

# 2. 重启数据库服务

    systemctl restart mariadb

# 3. 进入数据库命令管理界面

    mysql

    use mysql

    update user set password=password('123456') where user='root' and host='localhost';

    flush privileges;

# 4.还原配置文件

    vim /etc/my.cnf

 

数据库用户管理

# 创建用户

　　create user admin@'%' identified by '123456';

# 授权 all位置还可以填insert、update、delete、select， *.* 第一个*代表所有数据库，第二个*代表所有表，'%'代表任意IP地址链接，with option代表有权向下授权

　　grant all on *.* to admin@'%' with option;

# 刷新授权表

　　flush privileges;

# 回收权限

　　revoke all on *.* from admin@'%';

# 查看授权信息

　　show grants for admin@'%';

 

主从数据库

主数据库

# 追加修改配置文件

    vim /etc/my.cnf

        server-id = 100 

# 重启服务

    systemctl restart mariadb

# 从数据库用户指定

    >grant replication client,replication slave on *.* to repl@'%' identified by '123456';

    >flush privileges

    >show master status\G;

# 记录下Position的值，从数据库读取日志文件需要

 

从数据库

# 追加修改配置文件

    vim /etc/my/cnf

        relay-log = /var/lib/mysql/relay-log
        server-id = 200 

# 重启数据库服务

    systemctl restart mariadb

# 指定主数据库

    change master to master_host='198.168.198.128',master_user='repl',master_password='123456',master_log_file='bin-log.000004',master_log_pos=1642;

    slave start;

    show slave status\G;

 

数据库备份

使用备份工具

# 创建测试数据库，表，记录

    create database test charset='utf8';

    use test;

    create table tb_test( id int primary key auto_increment, name char(10) not null, age int not null, gender enum('男', '女') not null, description char(128));

    desc tb_test;

    insert into tb_test (name, age, gender, description) values ('张三', 33, '男', '无业游民');

    insert into tb_test (name, age, gender, description) values ('李四', 44, '男', 'CEO');

    insert into tb_test (name, age, gender, description) values ('王五', 22, '男', '富二代');

    select * from tb_test;


# 全库备份

    mysqldump -uroot -p123456 --all-databases --lock-all-tables --flush-logs > all.dump

# 单库备份，bin-log.000004为号码最大的，即最新备份日志
    
    cd /var/lib/mysql/

    mysqlbinlog -d test bin-log.000004 >  /mysql_backup/increment.dump

# 单库全备份

    mysqlbinlog bin-log.000004 >  /mysql_backup/increment_all.dump

 

恢复数据库

# 模拟数据库遭遇损坏

    cd /var/lib/mysql

    rm -rf ./*

# 重启数据库服务

    systemctl restart mariadb 

# 重新初始化安装

    mysql_secure_installation

# 由于准备要使用备份文件恢复数据库，所有要先关闭二进制日志功能，避免对恢复过程做无用的备份

    mysql -uroot -p123456

    >set sql_log_bin = 0;

# 恢复

    # 方式一

    # 使用之前的全备文件恢复

    mysql -uroot -p123456 < all.dump

    # 方式二

    # 使用增量文件恢复

    >source increment.dump

 

逻辑卷快照配备数据库

# 准备逻辑卷，可以参考本人博客

    fdisk /dev/sdc

    pvcreate /dev/sdc

    vgcreate vg0 /dev/sdc

    lvcreate -L 2000M -n lvm vg0

# 制作文件系统

    mkfs.ext4 /dev/vg0/lvm 

# 配置自动挂载

    blkid /dev/vg0/lvm >> /etc/fstab

    vim /etc/fstab

        UUID="365a374e-17c9-4fbc-8920-01ed56b84fc7" /mnt/lvm ext4 defaults 0 0

    mkdir /mnt/lvm

    mount -a

    df -h 

#  创建数据库数据存储目录

    mkdir /mnt/lvm/data

# 修改目录权限

    chown mysql:mysql /mnt/lvm/data/

# 修改mysql配置文件

    vim /etc/my.cnf

        datadir = /mnt/lvm/data

# 为了集中讨论本文重点，这里关闭SELinux

    setenforce 0

# 重启服务

    systemctl restart mariadb

# 备份

    # 锁表

        mysql -uroot -p123456 -e'flush tables with read lock'

    # 逻辑快照

        lvcreate -L 2000M -n snapshoot -s /dev/vg0/lvm

    # 释放表

        mysql -uroot -p123456 -e'unlock tables'

 

在线备份工具percona-xtrabackup

安装

yum install -y percona-xtrabackup

 

创建备份文件目录

mkdir /mysql_backup

 

备份数据库

# 数据库全备

    innobackupex --user=root --password=123456 /mysql_backup

# 数据库增量备份

    innobackupex --user=root --password=123456 --incremental /mysql_backup --incremental-basedir=/mysql_backup/2018-01-25_18-50-59

# 将增量打包进全备文件

    innobackupex --apply-log --redo-only /mysql_backup/2018-01-25_18-44-35

    innobackupex --apply-log --redo-only /mysql_backup/2018-01-25_18-44-35 --incremental-dir=/mysql_backup/2018-01-25_18-50-59

    innobackupex --copy-back /mysql_backup/2018-01-25_18-44-35/