命令

说明

mysql -uroot -p -e "show variables like 'datadir';"

找到 MySQL 数据目录

1 .创建备份用户
CREATE USER 'backup_user'@'192.168.1.1' IDENTIFIED BY 'backup_user';
GRANT SELECT, LOCK TABLES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, EVENT, TRIGGER ON *.* TO 'backup_user'@'192.168.1.1';

-- 授权 
CREATE USER 'backup_user'@'192.168.1.2' IDENTIFIED BY 'backup_user';
GRANT SELECT, LOCK TABLES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, EVENT, TRIGGER ON *.* TO 'backup_user'@'192.168.1.2';
2. 创建并授权「主从复制用户」（backup1）
主从用户核心需 REPLICATION SLAVE 权限（复制必备）：

-- 授权 
CREATE USER 'backup1'@'192.168.1.1' IDENTIFIED BY 'backup1';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'backup1'@'192.168.1.1';

-- 授权
CREATE USER 'backup1'@'192.168.1.2' IDENTIFIED BY 'backup1';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'backup1'@'192.168.1.2';

3. 刷新权限（生效授权）
FLUSH PRIVILEGES;

4. 验证用户创建结果
-- 查看用户和授权 IP
SELECT user, host FROM mysql.user WHERE user IN ('backup_user', 'backup1');

创建并授权备份用户

SELECT
  TABLE_SCHEMA AS '库名',
  TABLE_NAME AS '表名',
  ROUND(DATA_LENGTH/1024/1024, 2) AS '数据大小(MB)',
  ROUND(INDEX_LENGTH/1024/1024, 2) AS '索引大小(MB)',
  ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024, 2) AS '总大小(GB)',
  TABLE_ROWS AS '行数'
FROM information_schema.TABLES
ORDER BY (DATA_LENGTH+INDEX_LENGTH) DESC
LIMIT 50;

查「所有库」的表大小

SELECT
  TABLE_NAME AS '表名',
  ROUND(DATA_LENGTH/1024/1024, 2) AS '数据大小(MB)',
  ROUND(INDEX_LENGTH/1024/1024, 2) AS '索引大小(MB)',
  ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024, 2) AS '总大小(GB)',
  TABLE_ROWS AS '行数'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'hwms_wallet' AND TABLE_TYPE = 'BASE TABLE'
ORDER BY (DATA_LENGTH+INDEX_LENGTH) DESC LIMIT 20;

查「指定库」的表大小（按总大小排序，查test库中表的大小，按总大小（数据+索引）降序，取前20