mysql日常运维

centos安装mysql

1
2
3
4
5
6
7
8
9
10
11
12

#下载文件
mwget http://cdn.mysql.com/Downloads/MySQL-5.6/MySQL-5.6.25-1.linux_glibc2.5.x86_64.rpm-bundle.tar

#查询是否安装
rpm -qa|grep mysql

#删除已安装的mysql
rpm -e -allmatches -nodeps mysql-libs-5.1.73-3.el6_5.x86_64

#启动mysql
/etc/init.d/mysql start

mysql无法登陆

1
2
3
4
5
6
7
8
9
10
11
12

# /etc/init.d/mysql stop
# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
# mysql -u root
mysql> use mysql
mysql> UPDATE user SET Password=PASSWORD('root') where USER='root';
mysql> FLUSH PRIVILEGES;
mysql> quit
# /etc/init.d/mysql restart
# mysql -uroot -p
Enter password: <输入新设的密码newpassword>
mysql> SET PASSWORD = PASSWORD('newpasswd');

导出csv文件

1
2

mysql icms -uroot -e "SELECT column AS '列名' FROM table " >bond.xls

导出数据库

1
mysqldump -uroot -ppassword dbname > dbname-backup-$(date +%Y%m%d).sql

授权并创建用户

1
2
grant all on dbname.* to root@'localhost' identified by 'pass';
flush privileges;

删除用户

1
drop user user@'%';