mysql bullshit

mysql, quick reference

reserved column names need to be escaped with a backtick

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
ADD
AND
BEFORE
BY
CALL
CASE
CONDITION
DELETE
DESC
DESCRIBE
FROM
GROUP
IN
INDEX
INSERT
INTERVAL
IS
KEY
LIKE
LIMIT
LONG
MATCH
NOT
OPTION
OR
ORDER
REFERENCES
SELECT
TABLE
TO
UPDATE
WHERE

To insert a god damn constraint

1
2
3
PRIMARY KEY (`id`),
 KEY `instance_uuid` (`instance_uuid`),
 CONSTRAINT `instance_system_metadata_ibfk_1` FOREIGN KEY (`instance_uuid`) REFERENCES `instances` (`uuid`)

1
mysql> insert into instance_system_metadata (created_at, updated_at, deleted_at, instance_uuid, `key`, value, deleted) values (NOW(), default, default, (select uuid from instances where uuid = '0bd1a965-e401-46e7-b357-08fa2e324be9'), 'image_numa_nodes', '2', 0);

random live setting changes

1
2
3
4
5
6
7
8
set global slow_query_log_file = '/var/lib/mysql/slow_queries.log';
SET GLOBAL slow_query_log = 'ON';
set global long_query_time = 1;
set global log_output = '/var/lib/mysql/general.log';
set global log_output = 'FILE';
SET global general_log_file='/var/lib/mysql/general.log';
SET global general_log = 1;
SET global general_log = 0;

Show user’s and hosts in MySQL

1
SELECT User,Host FROM mysql.user

Pull a table out of a sql dump

1
sudo awk 'BEGIN{RS="\n\n"; tableNameVar="tablenamegoeshere"} $3 ~tableNameVar || $5 ~tableNameVar{print $0}' /path/to/sql/file.sql

Example insert

1
INSERT INTO records ( id, name, ttl, priority, content, type ) VALUES ('$domain_id','$hostname','$ttl','$priority','$content','$type')

MyISAMchk (stop mysql first)

1
myisamchk -fce /var/lib/mysql/$db/$table.MYI

convert ip to int easily

1
select ip, ipid from ip where INET_NTOA(ip) = ('104.28.0.93')