mysql建用户、主从常用命令记录

mysql建用户、主从常用命令记录

 

-- 规范化创建用户, 5.6/5.7/8.0
create user 'user_name'@'192.168.2.100' identified by 'iLtJokrjkhJSELO55Yz9';
grant insert,delete,update,select on  db_name.* to 'user_name'@'192.168.2.100';

-- 8.0因为需要和当前的客户端兼容，除了上述方外，推荐如下写法
create user 'user_name'@'192.168.2.100' identified WITH mysql_native_password by 'iLtJokrjkhJSELO55Yz9';
grant insert,delete,update,select on  db_name.* to 'user_name'@'192.168.2.100';

-- 8.0 新建用户，设置账户过期时间方法
create user 'user_name'@'192.168.2.100' identified WITH mysql_native_password by 'iLtJokrjkhJSELO55Yz9' PASSWORD EXPIRE INTERVAL 180 DAY;
grant insert,delete,update,select on  db_name.* to 'user_name'@'192.168.2.100';

-- 8.0 对于已经存在的用户，设置账户过期时间方法
-- create user 'user_name'@'192.168.2.100' identified WITH mysql_native_password by 'iLtJokrjkhJSELO55Yz9';
-- grant insert,delete,update,select on  db_name.* to 'user_name'@'192.168.2.100';
alter user 'user_name'@'192.168.2.100' PASSWORD EXPIRE INTERVAL 180 DAY;
mysql> select user,host,password_lifetime from mysql.user where user='user_name';
+-------------+---------------+-------------------+
| user        | host          | password_lifetime |
+-------------+---------------+-------------------+
| user_name | 192.168.2.100 |               180 |
+-------------+---------------+-------------------+
1 row in set (0.00 sec)

-- 5.6/5.7加密后的账号迁移至8.0的方法
create user 'user_name'@'192.168.2.100'  IDENTIFIED WITH 'mysql_native_password' AS '*A288238B17060F0F65DBD51CC6FFC58B12151068';
grant insert,delete,update,select on db_name.* to 'user_name'@'192.168.2.100';


-- 修改密码方式

  alter user 'user_name'@'10.10.10.%' identified with mysql_native_password by 'userpwd';

-- 根据现有的账号密码，新建授权同名但ip不同的账号权限方式

create user 'user_name'@'10.10.10.%' IDENTIFIED WITH 'mysql_native_password' AS '*A288238B17060F0F65DBD51CC6FFC58B12151068';
grant insert,delete,update,select on db_name.* to 'user_name'@'10.10.10.%';

-- 删除账号通用方法
-- 不要使用delete  mysql.user where user='user_name'方式，会删除不干净
drop user 'user_name'@'192.168.2.100'; 

# 基于pos点搭建主从同步命令
CHANGE MASTER TO
  MASTER_HOST='192.168.2.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl123',
  MASTER_PORT=3306,
  master_log_file='mysql-bin.000004', 
  master_log_pos=194;
  
  start slave;
  show slave status\G; 

# 基于gtid搭建主从命令
CHANGE MASTER TO
  MASTER_HOST='192.168.2.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl123',
  MASTER_PORT=3306,
  MASTER_AUTO_POSITION = 1;
  
  start slave;
  show slave status\G;

# 多源复制搭建命令
CHANGE MASTER TO
  MASTER_HOST='192.168.2.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl123',
  MASTER_PORT=3306,
  master_log_file='mysql-bin.000399', 
  master_log_pos=154
  FOR CHANNEL 'db_name';
  
  start slave FOR CHANNEL 'db_name';
  show slave status FOR CHANNEL 'db_name'\G; 

# 导出单库常用命令,无gtid
mysqldump --defaults-extra-file=/home/mysql/mysql/etc/.user.root.cnf --single-transaction --flush-privileges --lock-tables  --master-data=2 --flush-logs --triggers --routines --events --hex-blob  --max_allowed_packet=67108864 db_name >db_name.dump.sql 

# 导出全库常用命令
mysqldump --defaults-extra-file=/home/mysql/mysql/etc/.user.root.cnf --single-transaction --flush-privileges --lock-tables  --master-data=2 --flush-logs --triggers --routines --events --hex-blob  --max_allowed_packet=67108864 --all-databases >full.backup.dump.sql 

# 根据实际情况,考虑是否使用  --set-gtid-purged=on  --set-gtid-purged=off