search by tags

for the user

adventures into the land of the command line

mysql cheat sheet

installing mysql and starting it

$ yum install mysql mysql-server php-mysql MySQL-python
$ /etc/init.d/mysqld start
$ mysql_secure_installation

    When prompted, enter a password for the root account.

    Enter the current root password. By default, the root
    account does not have a password set, so press Enter.

    Type Y to set a password, and enter a secure password
    twice. For more information about creating a secure
    password, go to http://www.pctools.com/guides/password/.
    Make sure to store this password in a safe place.

    Type Y to remove the anonymous user accounts.

    Type Y to disable remote root login.

    Type Y to remove the test database.

    Type Y to reload the privilege tables and save your
    changes.

$ chkconfig mysqld on
$ /etc/init.d/mysqld restart

logging in

$ mysql -u root -p

databases

create a database

mysql> CREATE DATABASE somedb;

for idempotency

mysql> CREATE DATABASE IF NOT EXISTS somedb;

show all databases

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| somedb             |
| mysql              |
+--------------------+

select a database

mysql> USE somedb;

remove a database

mysql> DROP somedb;

users & permissions

create a user that exists on the localhost with a password

mysql> CREATE USER 'someuser'@'localhost' IDENTIFIED BY 'somepassword';

create a user that exists on a remote host with a password

mysql> CREATE USER 'someuser'@'10.1.2.3' IDENTIFIED BY 'somepassword';

show users

mysql> SELECT user FROM mysql.user;
+-------------+
| user        |
+-------------+
| root        |
| someuser    |
+-------------+

grant permissions to a local or remote user

mysql> GRANT ALL ON somedb.* TO 'someuser'@'localhost';
mysql> GRANT ALL ON somedb.* TO 'someuser'@'10.1.2.3';

show permissions for the current user

mysql> SHOW GRANTS;
+--------------------------------------------------------+
| Grants for [email protected]                              |
+--------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*F861720E101148897B0F5239DB926E756B1C28B3' WITH GRANT OPTION
+--------------------------------------------------------+

show permissions for a specific user

mysql> SHOW GRANTS FOR 'someuser'@'localhost';
+--------------------------------------------------------+
| Grants for [email protected]                       |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'someuser'@'localhost' IDENTIFIED BY PASSWORD '*74FC57D555BA199E75784F2B30D82BBE780891CD'              |
+--------------------------------------------------------+
| GRANT ALL PRIVILEGES ON `somedb`.* TO 'someuser'@'localhost'
+--------------------------------------------------------+

some (but not all) privilege keywords that can be granted

ALL [PRIVILEGES]    
ALTER               
CREATE              
CREATE USER         
DELETE              
DROP                
GRANT OPTION        
INSERT              
LOCK TABLES         
RELOAD              
REPLICATION CLIENT
REPLICATION SLAVE   
SELECT              
SHOW DATABASES      
UPDATE              
USAGE

reloads the privileges from the grant tables in the ‘mysql’ database

mysql> FLUSH PRIVILEGES;

revoke permissions for a user

mysql> REVOKE ALL PRIVILEGES ON `somedb`.* FROM 'someuser'@'localhost';

remove a user

mysql> DROP USER [email protected];

tables

create a table, the syntax is column name, data type, default value.

mysql> USE somedb;
mysql> CREATE TABLE IF NOT EXISTS sometable(
id INT AUTO_INCREMENT,
bucket_owner VARCHAR(255) DEFAULT '-',
bucket VARCHAR(255) DEFAULT '-',
logged_time VARCHAR(255) DEFAULT '-',
remote_ip VARCHAR(255) DEFAULT '-',
requester VARCHAR(255) DEFAULT '-',
request_id VARCHAR(255) DEFAULT '-',
operation VARCHAR(255) DEFAULT '-',
`key` VARCHAR(255) DEFAULT '-',
request_uri VARCHAR(255) DEFAULT '-',
http_status SMALLINT signed DEFAULT '0',
error_code VARCHAR(255) DEFAULT '-',
bytes_sent MEDIUMINT signed DEFAULT '0',
object_size MEDIUMINT signed DEFAULT '0',
total_time SMALLINT signed DEFAULT '0',
turn_around_time SMALLINT signed DEFAULT '0',
referrer VARCHAR(255) DEFAULT '-',
user_agent VARCHAR(255) DEFAULT '-',
version_id VARCHAR(255) DEFAULT '-',
PRIMARY KEY(id)
);

show all tables in a database

mysql> SHOW TABLES IN somedb;
+-------------------+
| Tables_in_somedb  |
+-------------------+
| sometable         |
+-------------------+

show columns from a table in a database

mysql> SHOW COLUMNS IN sometable;

show all data from a table in a database

mysql> SELECT * FROM sometable;

show data from a specific column in a table in a database

mysql> SELECT bucket FROM sometable;
mysql> SELECT DISTINCT remote_ip FROM sometable;
mysql> SELECT SUM(bytes_sent) FROM sometable;
mysql> SELECT logged_time FROM sometable ORDER BY id DESC LIMIT 1
mysql> SELECT logged_time, remote_ip FROM somedb.sometable;
mysql> SELECT COUNT(*) FROM sometable WHERE request_uri LIKE '%PUT%' OR '%COPY%' OR '%POST%' OR '%LIST%' AND logged_time >= '[01/Feb]' AND logged_time

remove all data from a table

mysql> TRUNCATE DATABASE somedb;

edit the data in columns in a table

mysql> INSERT INTO sometable(logged_time) VALUES("10/Mar/2015:06:38:45 +0000");

mysql> UPDATE sometable SET logged_time = REPLACE(logged_time, '[', '');

edit the actual columns

mysql> ALTER TABLE sometable CHANGE time logged_time VARCHAR(255) DEFAULT '-';

remove a table

mysql> DROP TABLE sometable;

replication

export all databases

$ mysqldump --all-databases --lock-all-tables -p > dump.db

import a database dump

$ mysql -u root -p < dump.db

put all tables into read-only mode

mysql> FLUSH TABLES WITH READ LOCK;

show the master’s binary log coordinates

mysql> SHOW MASTER STATUS;
+-----------------+--------+------------+----------------+
| File            |Position|Binlog_Do_DB|Binlog_Ignore_DB|
+-----------------+--------+------------+----------------+
| mysql-bin.000002|107     |            |                |
+-----------------+--------+------------+----------------+

show the slave’s status

mysql> SHOW SLAVE STATUS\G;

some slave commands

mysql> START SLAVE;
mysql> RESET SLAVE;
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.1.2.3',     <- master servers ip address
-> MASTER_USER='repl',         <- replication user
-> MASTER_PASSWORD='somepassword', <- password for the user
-> MASTER_LOG_FILE='mysql-bin.000002', <- binary log file
-> MASTER_LOG_POS=107;                 

config files & logs

config file location

/etc/my.cnf

by default, mysql is installed into /var/lib/mysql/ unless you change stuff in the config file.

most of the log files you’ll see, only the error log is on by default, unless you change stuff in the config file. if replication is being used, the binary log is required. by default log file names are the hostname.err or whatever, and are in mysql’s home directory unless you change them in the config file.

error log # problems encountered starting, running, or stopping mysqld
general query log   # established client connections and statements received from clients
binary log # statements that change data (also used for replication)
relay log   # data changes received from a replication master server
slow query log # queries that took more than long_query_time seconds to execute