基本命令
- mysql -uroot -ppass -h1.1.1.1 -Pport
- show status; 显示数据库基本信息 可以用like 限制
- show processlist; 显示链接信息
- show databases; 显示所有数据库
- use dbname; 进入某数据库
- show tables [from dbname]; 显示所有表
- desc tablename; 显示表结构
- show create table tablename; 显示表创建SQL
- show index from tablename; 查看表索引
- show warnings; 显示执行的警告 errors
- CREATE USER rep IDENTIFIED BY ‘123456’; 创建用户
- grant replication slave on . to
rep@192.168.253.%; 授权 - FLUSH PRIVILEGES; 刷新配置
- FLUSH tables with read lock; 写盘并锁住数据库
- unlock tables; 解锁数据库
- show master status; 查看主服务器状态
- select user,host,plugin,authentication_string,password_expired from mysql.user;
mysqldump
- mysqldump -hlocalhost -uroot -ppasswd -Pport dbname table > name.sql
-
mysqldump -uroot -p’123456’ -S /data/3306/data/mysql.sock –all-databases gzip > /server/backup/mysql_bak.$(date +%Y%m%d%H%M%S).sql - mysql -uroot -p’123456’ -S /data/3306/data/mysql.sock < /server/backup/mysql_bak.2015-07-01.sql
-
gunzip < /server/backup/mysql_bak.2015-07-01.sql mysql mysql -uroot -p’123456’ -S /data/3306/data/mysql.sock
mysqladmin
- mysqladmin shutdown
- mysqladmin stop-slave
- mysqladmin start-slave
docker
- docker run -itd –name redmin-mysql –restart=always –network redmin-net /data/mysql/config:/etc/mysql/conf.d /data/mysql/data:/var/lib/mysql -e MYSQL_USER=redmine -e MYSQL_PASSWORD=secret -e MYSQL_DATABASE=redmine -e MYSQL_ROOT_PASSWORD=123456 mysql:8.0.30
主备 8.0以下
master:1.1.1.1
CREATE USER 'sync'@'%' IDENTIFIED BY '123456';
grant replication slave on *.* to 'sync'@'2.2.2.2';
flush privileges;
master:1.1.1.1
[client]
port = 3306
socket = /var/run/mysqld/mysql.sock
[mysqld]
server-id=1
log-bin=master-bin
log-bin-index=master-index-bin
binlog_do_db=redmin //repeat mutil line to set mutil db
binlog_ignore_db=temp //repeat
sync_binlog=1 //每次写盘后事物才完成
slave:2.2.2.2
[mysqld]
server-id=2
relay-log=slave-relay-bin
relay-log-index=slave-relay-index-bin
replicate-do-db=redmin //repeat must same master
replicate-ignore-db //repeat
replicate-wild-ignore-table=mysql.* 忽略的表
replicate-wild-ignore-table=sys.* 忽略的表
read_only = ON
skip_slave_start= ON 使用手动启动
sync_relay_log = 1 每隔多少事件刷新一次磁盘
sync_relay_log_info=1 每隔多少事物刷新一次info
master:1.1.1.1
show master status;
slave:2.2.2.2
change master to master_host='1.1.1.1',master_port=3306,master_user='sync',master_password='123456',master_log_file='master-bin.000001',master_log_pos=2196,master_connect_retry=10,master_retry_count=86400;
start slave; //开启同步
stop slave; //停止同步
reset slave; //重置同步
show slave status \G; //查看状态
主从切换
slave:2.2.2.2
stop slave io_thread;
show processlist;//Slave has read all relay log; waiting for the slave I/O thread to update
show slave status \G; ->Master_Info_File 位置打开查看第一行post值
登陆其他从库
show slave status \G; 找到post最大的从库
登陆post最大的从库
stop slave;
drop table mysql.slave_master_info;
create user '用户名'@'从数据库ip地址' identified with mysql_native_password by '用户密码';
grant replication slave on *.* to '用户名'@'从数据库ip地址';
flush privileges;
进入数据库数据目录 rm -f *relay* 删除relay log
最后重新配置主从
从库冷备份
mysqladmin stop-slave || mysql -e `stop slave sql_thread;`
mysqldump --all-databases > xxx
mysqladmin start-slave
默认异步主备
半同步备份
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" 安装半同步插件
rpl-semi-sync-master-enabled = 1 启用半同步Master插件
rpl-semi-sync-slave-enabled = 1 启用半同步slave插件
rpl_semi_sync_master_wait_no_slave = 1 至少有一台bin-log同步后事物结束
延迟复制
在多从的情况下，可以有1-2台服务器设置延迟复制，保证错删数据库时，从库不会立即也删除自身
change master to master_delay = 3600
主备 8.0以上
master:1.1.1.1
[mysqld]
server-id=1
gtid_mode = on
enforce_gtid_consistency = true
log_bin=master-bin
log-slave-updates=on
CREATE USER 'sync'@'%' IDENTIFIED BY '123456';
grant replication slave on *.* to 'sync'@'2.2.2.2';
flush privileges;
mysql -uroot -proot -e "show variables like 'gtid_mode'"
mysqldump -h 192.168.56.201 -uroot -pxxxxx --all-databases --routines --events --triggers --single-transaction --source-data --quick > db.sql
show binlog events in 'mysql-bin.000002'\G
slave:2.2.2.2
[mysqld]
server-id=2
gtid_mode = on
read_only=on
enforce_gtid_consistency = true
relay-log=slave-bin
log-slave-updates=on
master-info-repository=TABLE
relay-log-info-repository=TABLE
mysql -uroot -p < db.sql
change replication source to source_host='1.1.1.1',source_user='sync', source_password='xxx',source_auto_position=1,MASTER_SSL=1,GET_MASTER_PUBLIC_KEY=1,MASTER_PUBLIC_KEY_PATH='/var/lib/mysql/public_key.pem';
start replica;
show replica status \G
#切换
mysql -uroot -p
stop slave;
reset slave all;
set global read_only=off;
exit
