linux网络服务--MySQL服务管理(用户与权限管理，备份，集群）

Lniux服务管理–MySQL

一、基础命令：

用户相关：

# 创建管理员用户
mysql> mysqladmin -uroot password 'password';

# 创建用户并设置密码，'@'指定用户可登录的终端
mysql>create user username@‘ip’ identified by ‘password’;  # @‘%’ 指任意终端

# 远程登录
> mysql -u username -ppassword  -h 服务器ip

# 设置密码
mysql>set password=password('new_password'); # 为自己更改密码
mysql>set password for username@‘%’=password(‘new_password’); # root用户更改普通用户的密码
# 找回root用户密码方式： 在mysql服务器/etc/my.cnf 中添加一行：skip-grant-tables，重启mysqld服务，免密登录后执行：
mysql>update mysql.user set password=password(‘new_password’) where user=’root’;

表相关：

# 建表
mysql>create table 表名 (字段名1 类型1 字段控制1，字段名2 类型2 字段控制2 ...);
Mysql>create table a2 (
    ->id int unsigned not null auto_increment, # 正数自增不为空
    ->name char(30) not null default ‘’,
    ->age int not null default 0,
    ->primary key (id));
    
# 查看表结构：
mysql>describe table_name; # 需先use database进入数据库
mysql>describe 数据库名.table_name;

# 修改表名
mysql>alter table 旧名 rename 新名;

# 删库,删表，删记录
mysql>drop database 数据库名;
mysql>drop table 表名;
mysql>delete from 表名 where ...;

数据、字段相关：

# 插入数据
mysql>insert into 表名(字段列表) values (值列表);
# 修改数据
mysql>update 表名 set 字段名='新值' where ...;

# 修改字段
mysql>alert table 表名 modify 字段名 新数据类型; # modify只修改字段类型
mysql>alert table 表名 change 字段名 新字段名 新数据类型 新字段控制;
mysql>alert table 表名 add 新字段名 新字段类型 [first | after 字段名]; # 添加新字段，[] 指定插入的位置
mysql>alert table 表名 drop 字符案名; #删除字段

授权相关：

# 授予权限
mysql>grant 权限名 on 库名.表名 to 用户名@ip;
mysql>grant 权限名 on 库名.表名 to 用户名@ip identified by 'password';#创建用户并授权

# 查询权限
mysql>show grants for  用户名@ip;

# 删除权限
mysql>revoke 权限列表 on 库名.表名 from 用户名@ip;

二、备份与还原

mysqldump：

# 备份
>mysqldump -u 用户名 -p 密码 库名[| --databases 库1，库2 (多库备份)| --all-databases(全量备份) | 库名 表1 表2(备份具体的表)] > 路径/备份文件名.sql


# 还原
>mysql -u 用户名 -p 密码 已有库名 < .sql 文件路径 # 需先创建数据库

mysqlhotcopy：

# 备份
>mysqlhotcopy --flushlog -u=’用户’-p=’密码’--regexp=正则备份目录
cp -a  数据目录 备份目录

# 还原
cp -a 备份目录 数据目录

日志备份

二进制日志（log-bin日志）：所有对数据库状态更改的操作（create、drop、update等）
修改服务器 /etc/my.cnf 文件加一行： log-bin=mysql-bin，启动二进制日志，日志文件在/var/lib/mysql下，重启mysqld服务

# 查看日志
>mysqlbinlog  mysql-bin.000001(二进制日志文件路径)
# at 4   # position
# 210115 20:08:48 server id 1  end_log_pos 106 	Start: binlog v 4, server v 5.1.73-log created 210115 20:08:48 at startup  # datetime

# 按时间还原（重复执行两个时间之间的命令）
>mysqlbinlog --start-datetime 'YY-MM-DD HH:MM:SS' --stop-datetime 'YY-MM-DDHH:MM:SS' 二进制日志文件路径| mysql -uroot -p password

# 按大小还原，使用一下参数
--start-position   --stop-position

三、MySQL集群

1. mysql主从备份，分摊查询压力

• 主从服务器分别开启二进制日志 ----> vi /etc/my.cnf

• 主服务器上授权

# grant replication slave on 库名.表名 to 授权用户名@从服务器ip; *.*表示所有数据库的所有表
> grant replication slave on *.* to user204@192.168.44.204;

授权后执行 show master status; 获取二进制日志信息

• 从服务器执行：

mysql> change master to
    -> master_user='user204',  # 授权用户名
    -> master_password='123456', # 密码
    -> master_host='192.168.44.203', # 主服务器ip
    -> master_log_file='mysql-bin.000002', # 二进制文件名（上面查的）
    -> master_log_pos=237; # 文件大小（上面查的）

执行成功后从服务器/var/lib/my.cnf 产生master.info文件：

[root@localhost mysql]# pwd
/var/lib/mysql
[root@localhost mysql]# ll  # 省略部分输出
总用量 20524
-rw-rw---- 1 mysql mysql       71 1月  15 20:45 master.info  # 此文件
[root@localhost mysql]# cat master.info 

• 从服务器开启功能，并查看

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G; # 查看从节点状态，省略部分输出
*************************** 1. row ***************************
             Slave_IO_Running: Yes  #开启
            Slave_SQL_Running: Yes  #开启

2.主主备份：主从方式下，两服务器互相为主备服务器，分担写压力。
3.多主一从：多个主服务器操作同上，从服务器设置多线程工作模式