Mysql some small commands and function about it.

# Dump one database or all the mysql to a sql file

mysqldump -u username -p databasename > somewhere.sql
mysqldump -u root --all-databases > mysql.sql

# Bring a sql file to mysql database
mysqldump -u root -p test < c:\a.sql

# Login Mysql
mysql -u root -p 

# Install Mysql
yum install -y mysql mysql-server

# Privileges
grant all privileges on *.* to admin@localhost identified by 'admin';

# Modify colume
use somedatabase;

alter table 表名 modify column 字段名 类型;
alter table SmokepingResult modify column ISPInfo varchar(100);

# Insert

insert into tablename set column_name=value1,column_name2=value2;
insert into Smokepint set ISPInfo="ChinaCache_BeiJing_58.68.148.25_ChinaMobile";

# Flush logs

shell> mv mysql-query.log mysql-query-old.log
shell> mysqladmin flush-logs
shell> mv mysql-query-old.log backup-directory

# Mysql-Slow Analysis
Edit File : /root/shells/backup_mysqld-slow.sh

#!/bin/bash
cd /var/log/mysqld/
cp -rvf mysqld-slow.log mysqld-slow.log.bak`date +%Y%m%d`
echo "" > mysqld-slow.log

Edit File : /root/shells/analysis_mysqld-slow.sh

#!/bin/bash
cd /var/log/mysqld
date1=`date --date="1 days ago" +%Y%m%d`
date2=`date --date="2 days ago" +%Y%m%d`
daterange="${date2}-${date1}"
filename1=mysqld-slow.log.bak${date1}
filename2=mysqld-slow.log.bak${date2}
echo $filename1
echo $filename2

# 分析
/usr/bin/pt-query-digest ${filename2} ${filename1} > analysis_mysqld-slow.log/${daterange}.txt

# 发送结果
cd /bin/
/usr/bin/php phpmail yonggew@staff.l99.com  "MySQL slow queries For $daterange" "Analysis Result In Attachment!" /var/log/mysqld/analysis_mysqld-slow.log/${daterange}.txt

# Do a cron job

# Added @ For mysqld-slow.log
0 3 * * * /bin/bash /root/shells/backup_mysqld-slow.sh
30 3 */2 * * /bin/bash /root/shells/analysis_mysqld-slow.sh

# If you can't find the mysql-slow log in /var/log/mysql/, you can do the following steps
1. connect to mysql
2. SET GLOBAL slow_query_log = 'OFF';
3. FLUSH LOGS;
4. SET GLOBAL slow_query_log = 'ON';

# If you forget your mysql password

# MySQL 忘记密码
/etc/init.d/mysqld stop
# 安全启动mysql
mysqld_safe --skip-grant-tables &
# 本地链接MySQL
mysql -u root
# flush password
use mysql;
update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
flush privileges;
quit;
# MySQL restart
/etc/init.d/mysqld restart
# Test
mysql -u root -p


# If you have low memory such as a vps with 128MB memory , you can do the following

Edit File : /etc/mysql/my.cnf
[mysqld]
# #innodb_buffer_pool_size=64M
default-storage-engine = MyISAM
skip-innodb