Linux中常用的MySQL運維腳本

在Linux中，使用MySQL進行常見的運維任務時，可以編寫一些腳本來簡化操作。以下是一些常用的MySQL運維腳本,希望對你的工作有所幫助.

備份數(shù)據(jù)庫：

?

#!/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

?

優(yōu)化和修復數(shù)據(jù)庫表：

?

#!/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

?

清除過期的備份文件：

?

#!/bin/bash

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

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

?

監(jiān)控MySQL進程狀態(tài)：

?

#!/bin/bash

MYSQL_USER="your_mysql_user"
MYSQL_PASSWORD="your_mysql_password"

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

?

監(jiān)控MySQL運行狀態(tài)信息：

?

#!/bin/bash

MYSQL_USER="your_mysql_user"
MYSQL_PASSWORD="your_mysql_password"

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

?

查看數(shù)據(jù)庫大小：

?

#!/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;"

?

導出數(shù)據(jù)庫表結(jié)構：

?

#!/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

?

導入數(shù)據(jù)庫表結(jié)構：

?

#!/bin/bash

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

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

	?

	鎖定MySQL數(shù)據(jù)庫：

	?
#!/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;"


	?

	解鎖MySQL數(shù)據(jù)庫：

	?
#!/bin/bash

MYSQL_USER="your_mysql_user"
MYSQL_PASSWORD="your_mysql_password"

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


	?

	請注意：在使用這些腳本之前，你需要替換占位符（your_mysql_user，your_mysql_password，your_database_name，/path/to/backup/directory 等）為適當?shù)闹怠?/p>

	　　審核編輯：湯梓紅

	?