1、修改mysql的root账号的密码(在忘记密码或者不知道密码的时候有用):
先停止mysql服务:
service mysqld stop 使用--skip-grant-tables忽略密码启动:
mysqld_safe --skip-grant-tables & 进入mysql修改密码:
use mysql
update user set password=password('mysql') where user='root';
flush privileges;
2、清理大表数据。
1)查看表占用空间情况:
select table_name, (data_length+index_length)/1024/1024 as total_mb, table_rows from information_schema.tables where table_schema='zabbix';
3、修改单独表空间:
1)备份数据
mysqldump -uroot -p zabbix > zabbix.sql 2)删除数据库:
drop database zabbix; 3)停止数据库:
service mysqld stop 4)删除共享表空间数据文件
$ cd /var/lib/mysql
$ rm ib* 5)增加innodb_file_per_table参数
vi /etc/my.cnf 6)在[mysqld]下设置
innodb_file_per_table=1
7)重启数据库:
service mysqld start 8)重新创建数据库:
mysql -uroot -p create database zabbix character set utf8;
grant all privileges on zabbix.* to zabbix@localhost identified by 'zabbix';
flush privileges;
9)导入zabbix数据库:
mysql -uzabbix -pzabbix zabbix/dev/null
下面是mysql_trunc_mysql.sh文件内容:
vim /backup/mysql_trunc_zabbix.sh #!/bin/bash
#truncate 3 big tables :trends_uint,history,history_unit .
#Every month's 1th 3:00 excute this script
#Writen by yuweibing ,phonenumber:18080116652
service zabbix_server stop
echo "truncate table trends_uint;" |mysql -uzabbix -pzabbix zabbix
echo "truncate table history;" |mysql -uzabbix -pzabbix zabbix
echo "truncate table history_uint;" |mysql -uzabbix -pzabbix zabbix
sleep 1
mysqldump -uroot -pmysql zabbix > /backup/zabbix.sql
if [ $? -eq 0 ]
then
echo "drop database zabbix;"|mysql -uroot -pmysql
else
exit 1
fi
echo "create database zabbix character set utf8;"|mysql -uroot -pmysql
echo "grant all privileges on zabbix.* to zabbix@localhost identified by 'zabbix';"|mysql -uroot -pmysql
echo "flush privileges;"|mysql -uroot -pmysql
mysql -uzabbix -pzabbix zabbix