Useful mysql command

Installation

Install mysql on rapberry pi

sudo apt-get install mysql-server mysql-client

Check version of mysql

mysql –version

Configure db

sudo vim /etc/mysql/my.cnf

Change password

SET PASSWORD FOR 'user name'@'localhost'='new password';

For later version, use authentication_string

Create user

GRANT ALL PRIVILEGES ON *.* TO 'your_username_here'@'localhost' IDENTIFIED BY 'your_chosen_password_here';
FLUSH PRIVILEGES;

Create database

CREATE DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Querying

List all tables in the database

SELECT * FROM information_schema.tables

or

show tables

Logging

Enable SQLs logging

An alternative way to log SQLs – logging to table. It is approriate when you don’t want to restart the sql server

1. Create a table for the log

  CREATE TABLE `slow_log` (
   `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 
                          ON UPDATE CURRENT_TIMESTAMP,
   `user_host` mediumtext NOT NULL,
   `query_time` time NOT NULL,
   `lock_time` time NOT NULL,
   `rows_sent` int(11) NOT NULL,
   `rows_examined` int(11) NOT NULL,
   `db` varchar(512) NOT NULL,
   `last_insert_id` int(11) NOT NULL,
   `insert_id` int(11) NOT NULL,
   `server_id` int(10) unsigned NOT NULL,
   `sql_text` mediumtext NOT NULL,
   `thread_id` bigint(21) unsigned NOT NULL
  ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
  
  CREATE TABLE `general_log` (
   `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
                          ON UPDATE CURRENT_TIMESTAMP,
   `user_host` mediumtext NOT NULL,
   `thread_id` bigint(21) unsigned NOT NULL,
   `server_id` int(10) unsigned NOT NULL,
   `command_type` varchar(64) NOT NULL,
   `argument` mediumtext NOT NULL
  ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'

1. Enable it by

SET global general_log = 1;
SET global log_output = 'table';

The log will be in the table general_log

1. view the log by select * from mysql.general_log
2. disable it when finished by SET global general_log = 0;

Export / import

Export database

mysqldump -u [username] -p [database name] > [database name].sql

Example:

15 2 * * * root mysqldump -u root -pPASSWORD –all-databases | gzip > /mnt/disk2/database_`date +”%Y%m%d”`.sql.gz

Import database

CREATE DATABASE newdatabase;

mysql -u [username] -p newdatabase < [database name].sql

Clone a database

mysqldump db_name | mysql new_db_name