mysql安装完成后一般密码都为空,需要初始化密码,具体命令如下:

mysql> /usr/local/mysql/bin/mysqladmin -u root password ‘xxx’

mysql> SELECT USER,HOST,PASSWORD FROM mysql.user;

mysql> use mysql;

mysql> update user set password=password(“xxxx”) where user=”root”;

mysql> delete from user where User=”” and Host=”localhost”;

mysql> delete from user where User=”” and Host=”localhost.localdomain”;

mysql> grant all privileges on *.* to ‘test’@’%’ identified by ‘xxxx’;

mysql> flush privileges;

mysql> SELECT USER,HOST,PASSWORD FROM mysql.user;

查看mysql用户

mysql> select user,host,password from mysql.user;

查看用户权限

mysql> show grants for root@localhost;

mysql密码破解:

在my.cnf中加入 skip-grant-tables,然后重启mysql,

进入修改用户密码:

mysql>use mysql

mysql>update user setpassword=password(“new_pass”) where user=”root”;

修改完了删除my.cnf中的skip配置,再重启mysql

PS:update user set host =’%’ where user = ‘root’;可以让任何用户访问,不仅仅只有本地用户

mysql导入导出及备份:

1.导出整个数据库

mysqldump -u 用户名 -p 数据库名 > 导出的文件名

mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql

2.导出一个表

mysqldump -u 用户名 -p 数据库名表名> 导出的文件名

mysqldump -u wcnc -p smgp_apps_wcncusers> wcnc_users.sql

把密码写到脚本里面会提示:

Warning: Using a password on the command line interfacecan be insecure.接口可以不安全

3.导出一个数据库结构(desc看到的表的结构

mysqldump -u wcnc -p -d –add-drop-table smgp_apps_wcnc >d:wcnc_db.sql

-d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table

4.如果需要导出mysql里面的函数或者存储过程

mysqldump -hhostname -uusername -ppassword -ntd -R databasename > backupflie.sql

mysqldump -hlocalhost -uroot -ntd -R hqgr > hqgr.sql 或者

mysqldump -hlocalhost -uroot -R hqgr > hqgr.sql

其中的 -ntd 是表示导出存储过程;-R是表示导出函数

5.mysql 只导出数据不导出结构(不包含建表语句)
mysqldump -t 数据库名 -uroot -p > xxx.sql
mysqldump -uxxx  -p’xxx’ -t  fahao > fahao_data.sql
 
6.mysql只导出数据库结构,不导出数据
mysqldump -uxxx  -p’xxx’ –no-data fahao > fahao.sql
mysqldump –opt -d 数据库名 -u root -p > xxx.sql
 
7.导出特定的表结构
mysqldump -uroot -p -B 数据库名 –table 表名 > xxx.sql  

常用source 命令:

进入mysql数据库控制台,

如mysql -u root -p

mysql>use 数据库

然后使用source命令,后面参数为脚本文件(如这里用到的.sql)

mysql>source d:wcnc_db.sql

mysql 导入:

还原数据  mysql -uroot -proot  databasename < mysqldump.sql

mysql -hhostname -uusername -ppassword databasename < backupfile.sql

eg:[root@localhost ~]#/usr/local/mysql/bin/mysql -h localhost -utest -p xx > xx.sql

删除数据库:

mysql> drop databasetest;

Query OK, 49 rows affected (1.84 sec)

重建库和表
用mysqldump –no-data把建表SQL导出来,然后drop database再create database,执行一下导出的SQL文件,把表建上;

删除表
mysql>drop TABLE MYTABLE;
清空表
mysql>delete from MYTABLE;

DELETE FROMtable1

开启mysql日志

[root@localhost log]#/usr/local/mysql/bin/mysql -V

/usr/local/mysql/bin/mysql  Ver 14.14 Distrib 5.1.39, for pc-linux-gnu (i686)using readline 5.1,配置方法:

wKioL1RgPbLx9hKCAADe59uvCa4693.jpg

long_query_time = 2,单位是s

查看慢日志相关信息:

mysql> show variables like’long_query_time’;

mysql> show status like ‘slow_queries’;

5.6 日志配置方法:mysql 5.6开启慢日志

#log

general_log = on

general_log_file = /usr/local/mysql/log/mysql.log

slow_query_log = on

slow_query_log_file =/usr/local/mysql/log/mysql-slow.log

long_query_time = 2             单位是s

#log=/usr/local/mysql/log/mysql.log

#long_query_time=2

#log_slow_queries=/usr/local/mysql/log/slowquery.log

开启二进制日志与回滚:

log-bin=/var/lib/mysql/mysql-bin.log # 二进制日志位置

# binary logging format – mixed recommended
binlog_format=mixed  #混合模式

max_binlog_size=500M   #当二进制日志达到500M后自动切割,默认1G自动切割
expire_logs_day=7  #只保留7天的二进制日志

 
导出二进制日志:
将一个mysqlbinlog文件导为sql文件
./mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 > /opt/001.sql
将mysql-bin.000001日志文件导成001.sql
 
可以在mysqlbinlog语句中通过–start-date和–stop-date选项指定DATETIME格式的起止时间
./mysqlbinlog –stop-date=”2009-04-10 17:41:28″ /usr/local/mysql/data/mysql-bin.000002 > /opt/004.sql
将mysql-bin.000002文件中截止到2009-04-10 17:41:28的日志导成004.sql
 
./mysqlbinlog –start-date=”2009-04-10 17:30:05″ –stop-date=”2009-04-10 17:41:28″ /usr/local/mysql/data/mysql-bin.000002  /usr/local/mysql/data/mysql-bin.0000023> /opt/004.sql
—-如果有多个binlog文件,中间用空格隔开,打上绝对路径
 source /opt/004.sql等同于下面这条:
./mysqlbinlog –start-date=”2009-04-10 17:30:05″ –stop-date=”2009-04-10 17:41:28″ /usr/local/mysql/data/mysql-bin.000002 |mysql -u root -p123456
 
将mysql-bin.000002日志文件中从2009-04-10 17:30:05到2008-04-10 17:41:28截止的sql语句导入到mysql中
 
—————————————————————————————————-
 
mysql查看有多少条数据?
select count(*) from table;
 
—————————————————————————————————-

PS:memcache启动命令:

memcached -d -p 11211 -u root -m 1024 -c 1024 -P /var/run/memcached.pid

/usr/bin/memcached -d -m 1024 -u root -l 127.0.0.1 -p 11211 -c 1024 -P /tmp/memcached.pid

 

 

2015.06.19更新

mysql不重启服务,开启慢查询日志方法:

首先查看下日志状态:

mysql> show variables like ‘%slow%’;
+—————————+———————————–+
| Variable_name | Value |
+—————————+———————————–+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+—————————+———————————–+
5 rows in set (0.04 sec)

开启慢查询开关:

mysql> set global slow_query_log = ON;
Query OK, 0 rows affected (0.12 sec)

设置慢查询记录的时间,超过2s的查询记录下日志中:

mysql> set global long_query_time = 2;
Query OK, 0 rows affected (0.00 sec)

设置慢查询日志文件,文件会跟库文件同一个目录,这里是/var/lib/mysql

mysql> set global slow_query_log_file = ‘mysql-slow.log’;
Query OK, 0 rows affected (0.08 sec)

设置完成后查看配置

mysql> show variables like ‘%slow%’;
+—————————+—————-+
| Variable_name | Value |
+—————————+—————-+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | mysql-slow.log |
+—————————+—————-+
5 rows in set (0.03 sec)

如果不需要记录慢查询了,可以关闭:

mysql> set global slow_query_log = 0;

重启mysql后也能生效,修改my.cnf文件:

log_error = mysql_error.log  # 开启错误日志

slow_query_log = on  #开启慢查询日志
slow_query_log_file =/usr/local/mysql/log/mysql-slow.log
long_query_time = 2

 

 

2015.06.29更新

mysql要绑定多个host写法:
mysql> grant all privileges on *.* to ‘hnb’@’192.168.0.% 27.17.47.198’ identified by ‘ray$#@!72gg’;
Query OK, 0 rows affected, 1 warning (0.04 sec)
clipboard1
 
mysql> delete from user where User=”hnb” and Host=”192.168.0.%,27.17.47.198,localhost”;
Query OK, 1 row affected (0.00 sec)
这样写也可以,但是测试用hnb用户无法登录,localhost也无法登录,说明上面的写法都不对,设置后用户无法正常登录。
最后修改为: (修改为如下后,测试hnb用户正常登录。)
mysql> grant all privileges on *.* to ‘hnb’@’192.168.0.%’ identified by ‘ray$#@!72gg’;
Query OK, 0 rows affected (0.00 sec)
clipboard2
 
 
 
 
2015.07.02更新
mysql怎么授权grant操作?
今天修改mysql用户权限发现一个很奇怪的问题,首先删除不用的用户
mysql> select user,host,password from mysql.user;
+——+———————–+———-+
| user | host | password |
+——+———————–+———-+
| root | localhost | |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | localhost.localdomain | |
+——+———————–+———-+
6 rows in set (0.01 sec)
 
mysql> delete from user where user=”” and Host=”localhost.localdomain”;
Query OK, 1 row affected (0.00 sec)

mysql> delete from user where user=”” and Host=”localhost”;
Query OK, 1 row affected (0.00 sec)

 
然后再添加相关用户;
QQ图片20150702181121
 
刷新权限记录:
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
感觉上面都没问题,但是再次用root用户进入数据库用grant来创建用户时,出现下面报错:
mysql> grant all privileges on *.* to ‘test’@’%’ identified by ‘123456’;
ERROR 1045 (28000): Access denied for user ‘root’@’%’ (using password: YES)
 
最后发现只能使用指定本地回环口,然后再用grant来创建用户成功
[root@localhost ~]# mysql -uroot -h127.0.0.1 -p
 
测试了很多遍,最后安装phpmyadmin来查看用户发现了问题:
QQ图片20150702181724
 
要打开grant授权才可以,具体sql如下:
mysql> use mysql
Database changed
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘root’@’localhost’ IDENTIFIED BY PASSWORD ‘*306A6F3680E03EBF7C104CF659566034988B3421’ WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

 
然后在刷新phpmyadmin用户,发现root被授权了,用grant创建用户后面需要加上WITH GRANT OPTION;创建的用户才能使用grant命令,否则无法使用grant命令,phpmyadmin上面用户显示授权为否
 

MySQL索引原理及慢查询优化

MySQL凭借着出色的性能、低廉的成本、丰富的资源,已经成为绝大多数互联网公司的首选关系型数据库。虽然性能出色,但所谓“好马配好鞍”,如何能够更 好的使用...

阅读全文

Mysql添加删除索引

使用索引,能够提高查询的速度 1、创建索引 1)ALTER TABLE 创建普通索引,UNIQUE索引或者PRIMARY KEY索引 ALTER TABLE table_name ADD INDEX index_name(colu...

阅读全文

nginx反向代理问题汇总及监控脚本

今天配置nginx的反向代理,配置文件如下: worker_processes 1; events { worker_connections 1024;} http { include mime.types; default_type application/...

阅读全文