MySQL

Note: MariaDB is a compatible fork of MySQL, it was started when Oracle became owner of MySQL development. This applies to both MySQL and MariaDB. For compatibility purposes MariaDB allows the use of MySQL commands.

Installation

sudo apt-get install mysql-server

Configure root password, or regain access

You need to configure the root password or maybe, you locked yourself out of access to the database root user. It happens. You can fix this only if you have root privileges on the machine, also, the database service has to be stopped temporarily.

#machine root user
sudo -i

#stop the database
service mariadb stop

#start the database in background without reading user info
mysqld_safe --skip-grant-tables &

#go into the sql prompt
mysql -u root

-- Update the password or other repairs:
  USE mysql
  update user set password=PASSWORD("**mysql-root-pwd**") where user='root';
  flush privileges;
  exit

#start the database
service mariadb start

Test your new password:

mysql -u root -p 

 Enter password: **mysql-root-pwd**
 \q

exit

GUI's

phpMySqlAdmin

• https://www.phpmyadmin.net/

MySQL Workbench

• http://www.mysql.com/products/workbench/

Easily installed using Ubuntu Software Center.

MySQL Workbench show BINARY as character string. Datatypes in the MediaWiki database are all binary, in order to view the contents do the following:

1. Go to Edit > Preferences
2. Choose SQL Queries
3. Under Query Results, check Treat BINARY/VARBINARY as nonbinary character string

MySql Administrator and Query Browser

sudo apt-get install mysql-gui-tools-common

Restart:

sudo service mysql stop
sudo service mysql start

Backup

The GUI (mysql-gui-tools-common) allow you to schedule backups as well.

You can use a simple command-line to create a compressed backup. The backup contains SQL statements that need to be executed to restore the database.

The following will backup all databases to one file.

mysqldump --user=root --password=**mysql-root-pwd** -A | gzip > /path/to/alldatabases.sql.gz

You can backup a single or a number of named databases with:

mysqldump --user=root --password=**mysql-root-pwd** --databases db1 db2 db3 | gzip > /path/to/databases.sql.gz

Protect your backup with a password (zip):

mysqldump --user=root --password=**mysql-root-pwd** --databases db1 | zip --password "**zip-pwd**" > /path/to/database_db1.sql.zip

Note that your commandline could be visible to other users along with the password.

Schedule a backup with crontab

To schedule a command we need to modify the crontab file:

sudo gedit /etc/crontab

A command is run when date/time matches the pattern. An asterisk (*) is used to match all. Examples:

# minute - hour - day - month - day of week - USER - COMMAND
* * * * * wilbert every_minute.sh
0 * * * * wilbert every_whole_hour.sh
0 0 * * * wilbert every_day_at_midnight.sh
0 0 1 * * wilbert every_1st_of_the_month_at_midnight.sh

We will run a backup check (mysqldump.sh) every hour:

0 * * * * root /root/mysqldump.sh

Using a subscript we can shield the password in the script from unwanted viewers. This script will run every hour and ensures only one backup is made each day:

sudo gedit /root/mysqldump.sh

#!/bin/bash
dumpfile="$home/mysql_$(date '+%Y-%m-%d').sql.gz"
if [ ! -f "$dumpfile" ] ; then
  mysqldump -u root -p**mysql-root-pwd** --all-databases | gzip > $dumpfile
fi

Make executable and protect it from being read by anyone but root:

sudo chmod 0700 /root/mysqldump.sh

Restore

Note: make a new full backup before you try to restore something.

Fully restore all databases:

mysql --user=root --password=**mysql-root-pwd** < alldatabases.sql

You can also start mysql prompt and run the script from there, you don't have to enter the password on the commandline:

sudo mysql --user=root

mysql> source alldatabases.sql

Restore one database

Only restore database mydatabase, MySQL scans for the USE statement in the sql-file to select the correct part.

mysql --user=root --password=**mysql-root-pwd** --one-database mydatabase < alldatabases.sql

Extract database using sed

Extract header and database respectively:

sed -n '/^-- MySQL dump/,/^-- Current Database: `/p' alldatabases.sql | head -n -1 > mydatabase.sql
sed -n '/^-- Current Database: `mydatabase`/,/^-- Current Database: `/p' alldatabases.sql >> mydatabase.sql

Extract database using a text editor

The backup file (in this example: alldatabases.sql) is really nothing more than a sequence of SQL statements. To restore a single database you need to select the continuous part that is the particular database.

For big text files such as these it is better to use a text editor like nano. Start a terminal and type:

nano alldatabases.sql

You can find the start of a database by searching for the text:

-- Current Database:

Use the following nano-shortcuts to remove the text that you won't use. Leave the header (the part that comes before the first database):

 ALT-A  start selection
 CTRL-W find text
 ESC \  first line
 ESC /  last line
 CTRL-K remove selected text
 CTRL-X exit, save

After you are done save the file 'mydatabase.sql'.

Restore MySQL / MariaDB root user

#root
sudo -i

#stop the service:
service mysql stop

#start without grant-tables
mysqld_safe --skip-grant-tables &

#sql prompt
mysql

Insert root user, use other name if it exists:

insert into `user` (`Host`, `User`, `Password`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Reload_priv`, `Shutdown_priv`, `Process_priv`, `File_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Show_db_priv`, `Super_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Execute_priv`, `Repl_slave_priv`, `Repl_client_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Create_user_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`) 
values('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','','0','0','0','0');

Database MariaDB access from other machine

For example to use mysqldump from another machine. There are two changes to be made (see below).

Disable specific client machine binding

Allow connections from other machines by disabling client machine binding, typically this is 127.0.0.1 (localhost) or in other words, the machine itself. If you didn't do this you would get an error like: can't connect to the server

Edit the following configuration file:

sudo nano +29 /etc/mysql/mariadb.conf.d/50-server.cnf

... #commented: #bind-address = 127.0.0.1 ...

Restart the service:

sudo systemctl restart mariadb

Allow a root connection from another, specific machine

You need to have a database user that is allowed to connect from other machines, you can designate specific IP addresses or ranges. If you didn't do this you would get an error like: host is not allowed to connect to this server.

sudo mysql --user=root

Here we allow a connection from machine with IP 192.168.1.8 to a user altroot. In this example we give full privileges, same as the root user.

-- select database
USE mysql;
-- create user
CREATE USER 'altroot'@'192.168.1.8' IDENTIFIED BY '**altroot*password**';
-- give full permissions
GRANT ALL PRIVILEGES ON *.* TO 'altroot'@'192.168.1.8' WITH GRANT OPTION;
-- re-read privileges
FLUSH PRIVILEGES;

If the user exists you can use 'rename'. For example allow a range of IP's using '%':

-- select database
USE mysql;
-- rename user
RENAME USER 'altroot'@'192.168.1.8' TO 'altroot'@'192.168.1.%' ;
-- re-read privileges
FLUSH PRIVILEGES;

Example: create a remote 'backup' user and allow local network access

In this example we allow only IP addresses starting with '192.168.1.'. We only give read privileges to this user.

sudo -i
mysql -u root mysql -p

CREATE USER 'backup'@'192.168.1.%' IDENTIFIED BY '**pwd**';
GRANT event, select, show databases, show view, trigger, lock tables, reload, file on *.* to 'backup'@'192.168.1.%';
FLUSH PRIVILEGES;
\q

Note: this example allows a mysqldump of all schemas

Info:

• http://patrickv.info/wordpress/2013/04/privileges-necessary-for-mysqldump/

Queries

Ten largest tables

SELECT CONCAT(table_schema, '.', table_name),
       CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows,
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
       ROUND(index_length / data_length, 2) idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  10;

Usage per schema

SELECT count(*) tables,
    table_schema,concat(round(sum(table_rows)/1000000,2),'M') rows,
    concat(round(sum(data_length)/(1024*1024*1024),2),'G') data,
    concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
    concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
    round(sum(index_length)/sum(data_length),2) idxfrac
    FROM information_schema.TABLES
    GROUP BY table_schema
    ORDER BY sum(data_length+index_length) DESC LIMIT 10
    ;