(在shell中)连接MySQL服务器: mysql -h host -u user -p
(在MySQL命令行客户端中)显示所有数据库: SHOW DATABASES;
,使用某个数据库: USE databasename;
,显示当前数据库中所有数据表: SHOW TABLES;
(在shell中)数据库备份: mysqldump -h host -u user -p dbname > /path/to/target_file.sql
,单独导出某个数据表: mysqldump -h host -u user -p dbname tablename > /path/to/target_file.sql
。
只导出数据表结构:使用mysqldump时加-d
选项
(在shell中)恢复数据库: mysql -h host -u user -p dbname < /path/to/source_file.sql
。
删除数据库的SQL语句: DROP DATABASE dbname
。
为数据库添加字段的SQL语句: ALTER TABLE `serverinfo` ADD COLUMN processlist varchar(1024) DEFAULT NULL COMMENT '进程列表' AFTER `alias`;
显示创建的SQL语句:
SHOW CREATE DATABASE db_name
SHOW CREATE TABLE table_name
服务器状态信息: SHOW STATUS
显示授权用户: SHOW GRANTS
显示服务器错误或警告信息: SHOW ERRORS/SHOW WARNINGS
修改MySQL数据库默认编码为UTF-8:在MySQL配置文件my.cnf中找到mysqld部分,在这部分中添加一句 character_set_server=utf8
,重启MySQL服务即可。
mysql线程等待时间,解决sleep进程过多的办法:http://blog.sina.com.cn/s/blog_78ecbe330101332k.html
MySQL用户创建与授权:
GRANT privileges ON databasename.tablename TO 'username'@'host'; // 如:GRANT ALL ON test.* TO 'pig'@'localhost';
查看环境变量:SHOW VARIABLES;
、SHOW VARIABLES LIKE '%size%';
SET PASSWORD FOR 'user_name'@'where_from' = PASSWORD('password');
。不过貌似不同版本的MySQL修改帐号密码的方式不一样。设置binlog的保持时长:SET GLOBAL expire_logs_days = 3;
,也可以在日志中mysqld
部分添加下面这行 expire_logs_days=3
;也可以在命令行中立即清理log:PURGE BINARY LOGS BEFORE (date(now()) + interval 0 second - interval 3 day);
。详情见:http://dba.stackexchange.com/questions/30930/how-soon-after-updating-expire-logs-days-param-and-restarting-sql-will-old-binlo
当某个字段的值为null,需要将其更新为空字符串:update cnvdvul set author="" WHERE author is null;
DROP USER 'username'@'host';
INSERT INTO Evil0day (`app`,`version`) SELECT `app`, `version` FROM Fingerprint WHERE app="joomla" AND version >="3.2.0" AND version <="3.4.4" GROUP BY `version`;
INSERT IGNORE INTO `BaseIndex` (`host`, `realtime`, `ex_realtime`, `history`, `ex_history`, `environment`, `ex_environment`, `attackrisk`, `ex_attackrisk`, `datestamp`) SELECT `host`, `realtime`, `ex_realtime`, `history`, `ex_history`, `environment`, `ex_environment`, `attackrisk`, `ex_attackrisk`, 20160412 FROM `BaseIndex` WHERE `datestamp`=20160411;
INSERT INTO TaitanData (host, fingerprint, hostscan, vul_num, datestamp) SELECT host, fingerprint, hostscan, vul_num, 20161009 FROM TaitanData WHERE host="btytgs.com" AND datestamp=20161006
来自:http://www.sohamkamani.com/blog/2016/07/07/a-beginners-guide-to-sql/
SELECT members.firstname || ' ' || members.lastname AS "Full Name"
FROM borrowings
INNER JOIN members
ON members.memberid=borrowings.memberid
INNER JOIN books
ON books.bookid=borrowings.bookid
WHERE borrowings.bookid IN (SELECT bookid FROM books WHERE stock> (SELECT avg(stock) FROM books) )
GROUP BY members.firstname, members.lastname;
SELECT title, bookid
FROM books
WHERE author IN (SELECT author
FROM (SELECT author, sum(stock)
FROM books
GROUP BY author) AS results
WHERE sum > 3);
SELECT
members.firstname AS "First Name",
members.lastname AS "Last Name",
count(*) AS "Number of books borrowed"
FROM borrowings
JOIN books ON borrowings.bookid=books.bookid
JOIN members ON members.memberid=borrowings.memberid
WHERE books.author='Dan Brown'
GROUP BY members.firstname, members.lastname;
SELECT DISTINCT(name), "host" FROM `clean_vul` WHERE `vul_class`=28 AND name NOT LIKE '%/%' INTO OUTFILE '/home/work/app_names.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
LOAD DATA INFILE '/home/work/app_names.csv' INTO TABLE app_name_mapper FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (old_app_name, type);
ALTER TABLE table_name
ADD INDEX [index_name] [index_type] (index_col_name,...) [index_option];
如:
ALTER TABLE Users
ADD INDEX (user_name);
SHOW COLUMNS FROM table_name;
或 DESCRIBE table_name;
SHOW STATUS;
SHOW GRANTS;
DESC
关键字只应用到直接位于其前面的列名。如果想在多个列上进行降序排序,必须对每个列指定DESC关键字。IS NULL
子句,如:SELECT prod_name FROM products WHERE prod_price IS NULL;
_
,用途与%
一样,但下划线只匹配单个字符而不是多个字符。Concat
函数用于拼接字符串,如:SELECT Concat(vend_name, ' (', vend_country, ')') FROM vendors ORDER BY vend_name;
RTrim()
、LTrim()
、Trim()
函数去除字符串两边的空格。SET GLOBAL max_connections=3000;
原因:我们测试环境的访问速度过慢,主要原因是DB服务器和Web服务器之间的网络太差,所以将DB服务也迁移到Web服务器上。
原MySQL版本:5.1.49,目标MySQL版本:5.6.19
步骤:
1.MySQL源码编译,见 http://dev.mysql.com/doc/refman/5.6/en/installing-source-distribution.html ,其中记得加系统账号mysql,设置数据目录权限,使用mysql_install_db进行数据库初始化
2.配置MySQL:
// 比如在mysqld部分添加以下两行
// 数据目录的路径
datadir=/var/lib/mysql
// 套接字文件路径
socket=/tmp/mysql.sock
3.修改用户密码,比如root的密码: mysqladmin -u root password 'new-password'
4.数据导入导出:
mysqldump -h host -u user -p dbname > /path/to/target_file.sql
CREATE DATABASE dbname DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci
,如果dbname属于MySQL的关键字,那么还得加反引号。mysql -h host -u user -p dbname < /path/to/source_file.sql
5.测试应用程序:由于新旧两个版本的MySQL版本差别较大,所以需要在一些细节上对应用程序做调整。
Got fatal error 1236 from master when reading data from binary log: ‘binlog truncated in the middle of event; consider out of disk space on master; the first event ‘mysql-bin.000525’ at 175770780, the last event read from ‘/data/mysql/repl/mysql-bin.000525’ at 175770780, the last byte read from ‘/data/mysql/repl/mysql-bin.000525′ at 175771648.’
The solution would be to move the slave thread to the next available binary log and initialize slave thread with the first available position on binary log as below:
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000526', MASTER_LOG_POS=4;
在主数据库上执行 SHOW BINARY LOGS
,找到距离错误的bin log文件位置最近的一个日志位置,然后执行上面的这条命令(记得替换参数MASTER_LOG_FILE和MASTER_LOG_POS)。
当主从同步出现问题时,可以先按照下面的步骤处理:
SHOW SLAVE STATUS
STOP SLAVE
SET GLOBAL sql_slave_skip_counter = N
START SLAVE
SHOW SLAVE STATUS
mysql> show slave hosts
-- 查看所有连接到Master的Slave信息mysql> show master status
-- 查看Master状态信息mysql> show slave status
-- 查看Slave状态信息mysql> show binary logs
-- 查看所有二进制日志在my.cnf中添加:
# 启用慢日志
slow_query_log = 1
# 记录执行时间大于等于5秒的SQL
long_query_time = 5
# 慢日志文件的路径
slow_query_log_file = /home/work/logs/mysql/mysqld.slow.log