Linux下最最常用的MySQL運維腳本

1. 備份數據庫：

#!/bin/bash

BACKUP_DIR="/path/to/backup/directory"
MYSQL_USER="your_mysql_user"
MYSQL_PASSWORD="your_mysql_password"
DATABASE="your_database_name"
DATE=$(date?+%Y%m%d%H%M%S)

mysqldump?-u?$MYSQL_USER?-p$MYSQL_PASSWORD?$DATABASE?>?$BACKUP_DIR/$DATABASE-$DATE.sql

2. 優化和修復數據庫表：

#!/bin/bash

MYSQL_USER="your_mysql_user"
MYSQL_PASSWORD="your_mysql_password"
DATABASE="your_database_name"

mysqlcheck?-u?$MYSQL_USER?-p$MYSQL_PASSWORD?--auto-repair?--optimize?$DATABASE

3. 清除過期的備份文件：

#!/bin/bash

BACKUP_DIR="/path/to/backup/directory"
DAYS_TO_KEEP=7

find?$BACKUP_DIR?-type?f?-mtime?+$DAYS_TO_KEEP?-exec?rm?{}?\;

4. 監控MySQL進程狀態：

#!/bin/bash

MYSQL_USER="your_mysql_user"
MYSQL_PASSWORD="your_mysql_password"

mysqladmin?-u?$MYSQL_USER?-p$MYSQL_PASSWORD?processlist

5. 監控MySQL運行狀態信息：

#!/bin/bash

MYSQL_USER="your_mysql_user"
MYSQL_PASSWORD="your_mysql_password"

mysqladmin?-u?$MYSQL_USER?-p$MYSQL_PASSWORD?extended-status

6. 查看數據庫大小：

#!/bin/bash

MYSQL_USER="your_mysql_user"
MYSQL_PASSWORD="your_mysql_password"
DATABASE="your_database_name"

mysql?-u?$MYSQL_USER?-p$MYSQL_PASSWORD?-e?"SELECT?table_schema?AS?'Database?Name',?SUM(data_length?+?index_length)?/?1024?/?1024?AS?'Size?(MB)'?FROM?information_schema.TABLES?GROUP?BY?table_schema;"

7. 導出數據庫表結構：

#!/bin/bash

MYSQL_USER="your_mysql_user"
MYSQL_PASSWORD="your_mysql_password"
DATABASE="your_database_name"

mysqldump?-u?$MYSQL_USER?-p$MYSQL_PASSWORD?--no-data?$DATABASE?>?schema.sql

8. 導入數據庫表結構：

#!/bin/bash

MYSQL_USER="your_mysql_user"
MYSQL_PASSWORD="your_mysql_password"
DATABASE="your_database_name"

mysql?-u?$MYSQL_USER?-p$MYSQL_PASSWORD?$DATABASE?

9. 鎖定MySQL數據庫：

#!/bin/bash

MYSQL_USER="your_mysql_user"
MYSQL_PASSWORD="your_mysql_password"

mysql?-u?$MYSQL_USER?-p$MYSQL_PASSWORD?-e?"FLUSH?TABLES?WITH?READ?LOCK;"

10. 解鎖MySQL數據庫：

#!/bin/bash

MYSQL_USER="your_mysql_user"
MYSQL_PASSWORD="your_mysql_password"

mysql?-u?$MYSQL_USER?-p$MYSQL_PASSWORD?-e?"UNLOCK?TABLES;"