MySQL

Installation

sudo apt-get install mysql-server

Configure MySQL root password

sudo -i
service mysql stop
mysqld_safe --skip-grant-tables &
mysql -u root

 USE mysql
 update user set password=PASSWORD("**mysql-root-pwd**") where user='root';
 flush privileges;
 \q

service mysql start

Test:

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.

Note: MariaDB is a compatible fork of MySQL, it was started when Oracle became owner of the MySQL database. This page should apply to both MySQL and MariaDB.

Problem: can't connect to the server

Comment bind-address to allow access from other machines:

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

...
#bind-address = 127.0.0.1
...

Restart database service:

sudo systemctl restart mariadb

Problem: Host '...' is not allowed to connect to this MariaDB server

Create 'backup' user and allow local network access.

Note: in this example we allow only IP addresses starting with '192.168.1.'.

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/

Allow connection with root from an other, specific machine

Connect as a root user.

-- create user
CREATE USER 'username'@'192.168.1.8' IDENTIFIED BY 'the_password';
-- give full permissions
GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'192.168.1.8' WITH GRANT OPTION;
-- re-read privileges
FLUSH PRIVILEGES;

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
    ;