mysql主从相关-白红宇

mysql主从相关

阅读量：6902 次

发布时间：2019-06-27

本文共 3906 字，大约阅读时间需要 13 分钟。

##mysql对数据库授权

mysql> GRANT ALL ON `db_cn`.* TO 'db_cn'@'192.168.3.%' IDENTIFIED BY 'xxxxxxxxxxxxx';

mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY '123456';

##mysqldump默认使用UTF8字符集备份

mysqldump -udatingdb_cn -p --databases datingdb_cn > datingdb_cn.sql

##找出支持引擎，Myisam作为默认的引擎

mysql> SHOW ENGINES;

##重置数据库root的密码

mysql -u root

mysql -uroot -S /var/data/mysql/master/mysql_master.sock ##指定sock登录

UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';

FLUSH PRIVILEGES;

##仅备份数据库的表结构

/usr/bin/mysqldump -uroot -S /var/data/mysql/master/mysql_master.sock --opt -R --databases datingdb_cn>datingdb_cn_`date +%Y_%H_%M`.sql

##完全备份数据库的方式

mysqldump --tab=/path/to/some/dir --opt db_name ##方式一

mysqlhotcopy db_name /path/to/some/dir ##方式二，只针对Myisam表

##完全备份所有数据库中的所有InnoDB表

mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql

##实现完全备份是清空Mysql的二进制日志，以至转存文件中包含新的当前二进制文件

mysqldump --single-transaction --flush-logs --master-data=2 --all-databases > backup_sunday_1_PM.sql

##完全备份时，清空不再使用的日志文件

##服务器为复制主服务器，--delete-master-logs删掉MySQL二进制日志很危险，从服务器可能还没完全处理该二进制日志的内容

mysqldump --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > backup_sunday_1_PM.sql

##完全备份、增量备份的恢复：

mysql < backup_sunday_1_PM.sql ##完全备份的恢复

mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql ##7，8为两个时间点的增量备份

##说明：

1. 定期进行完全备份，使用mysqldump命令进行在线非块备份

2. 用FLUSH LOGS或mysqladmin flush-logs清空日志进行定期增量备份

##Mysql主从复制的实现

(1)确保在服务器和从服务器上安装的MySQL版本一致

(2)主服务器上为服务器设置一个连接账户。该账户必须授予REPLICATION SLAVE权限

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'zxxxxxxxxxxx';

GRANT ALL PRIVILEGES on ** to 'db_cn'@'dev1.asianchats.com.cn' identified by 'zxxxxxxxxxxx';

GRANT ALL PRIVILEGES on *.* to 'db_cn'@'localhost' identified by 'sACt5zxPJ8t9PV8N';

flush privileges;

(3)执行语句清空所有表和块写入语句

mysql> FLUSH TABLES WITH READ LOCK;

##创建快照最简单的途径是使用归档程序对主服务器上的数据目录中的数据库进行二进制备份

tar -cvf /tmp/mysql-snapshot.tar .

tar -cvf /tmp/mysql-snapshot.tar ./this_db

##读锁定有效时，读取主服务器上当前的二进制日志名和偏移量值

(4)mysql > SHOW MASTER STATUS; ## mysql > SHOW SLAVE STATUS;

(5)##取得快照并记录日志名和偏移量后，可以在主服务器上重新启用写活动：

mysql> UNLOCK TABLES；

##使用InnoDB表的二进制快照来关闭主服务器并复制InnoDB数据文件、日志文件和表定义文件(.frm文件)。\

##要记录当前的日志文件名和偏移量，关闭服务器之前执行：

mysql> FLUSH TABLES WITH READ LOCK;

mysql> SHOW MASTER STATUS;

## 记录前面所示的SHOW MASTER STATUS的输出中显示的日志名和偏移量。记录日志名和偏移量后 \

## 不解锁表关闭服务器以确保服务器关闭时的快照与当前的日志文件和偏移量相对应

/etc/init.d/mysqld stop

##如果使用mysqldump备份主服务器的数据，将转储文件装载到从服务器

mysql -uroot -p yourdatabase < database_bak.sql

(6)授权操作

mysql> CHANGE MASTER TO MASTER_HOST='master_host_name',

-> MASTER_USER='replication_user_name',

-> MASTER_PASSWORD='replication_password',

-> MASTER_LOG_FILE='recorded_log_file_name',

-> MASTER_LOG_POS=recorded_log_position;

######################################################################################################################

myisampack,压缩MyISAM表以产生更小的只读表的一个工具

##如果由于结果较大而内存不足遇到问题，使用--quick选项。这样可以强制mysql从服务器每次一行搜索结果

mysql --quick

##对InnoDB作数据库转储时，先作表锁定操作

mysql > FLUSH TABLES WITH READ LOCK；

#Mysql slave群切换为主数据库操作

##说明：

1.主数据库主机受控(******)
2.主机物理损坏
3.系统环境
##切换过程：
(1) Master down
(2) Slave1 切换成新的Master
(3) Slave2 更换Master配置为原Slave2

1.从slave（slave1）群众中选定一个slave，准备换成master

2.检查slave1的复制状态
mysql> SHOW PROCESSLIST; ## 检查Slave1是否已经完成从Master读取relay log,若还处于读的状态，stop slave，数据将lost

3.停止slave1的slave进程，设置为master
mysql> STOP SLAVE;
mysql> RESET MASTER;

4.将slave 群中的其他slave(slave2)的Master切换成新的master(上述1,2,3所操作的master)
mysql> stop slave;
mysql> CHANGE MASTER TO
mysql> MASTER_HOST="192.168.10.x",
mysql> MASTER_USER='user',
mysql> ;
mysql> start slave;

5.检查slave的状态
mysql> show slave status \G;

6.检查复制是否成功
(1) 在新的Master创建测试表：
mysql> create table t1 as select * from test; ##已有的test数据库
mysql> show tables;
mysql> select count(*) from t1;
(2) 在slave2上面看是否已成功复制
mysql> show tables;
mysql> select count(*) from t1;