MySQL

From WickyWiki
Revision as of 19:28, 28 August 2013 by Wilbert (talk | contribs) (→‎Backup)


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

Install GUI's

Note: I prefer SQL Developer

Emma

sudo apt-get install emma

MySql Administrator and Query Browser

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

Restart: 

sudo service mysql stop
sudo service mysql start

SQL Developer

As a database tool I would definitely recommend SQL Developer. Although it mainly targets Oracle database and it is quite resource hungry, all its features greatly improve your interaction with the database.

Download the ZIP file from Oracle’s site titled “Oracle SQL Developer for other platforms” you will need an Oracle account:

We will assume it's name is 'sqldeveloper-3.0.04.34-no-jre.zip'.

Install the tofrodos package and create symbolic links to the tofrodos commands to allow the sqldeveloper-package to work: 

sudo apt-get install tofrodos
sudo ln -s /usr/bin/fromdos /usr/bin/dos2unix
sudo ln -s /usr/bin/todos /usr/bin/unix2dos

Install the make-sqldeveloper-package package. You will need to select Ubuntu’s multiverse software repository (not explained here): 

sudo apt-get install sqldeveloper-package

Build the .DEB package with the downloaded ZIP file: 

cd LOCATION_OF_ZIP_FILE
mkdir temp
make-sqldeveloper-package -b ./temp ./sqldeveloper-3.0.04.34-no-jre.zip

Install the package: 

sudo dpkg -i sqldeveloper_3.0.04.34+0.2.3-1_all.deb

Start SQL Developer: 

sqldeveloper

Look for the full pathname of a jvm installation with: 

update-alternatives --query java

Type full pathname of a jvm installation, when you use OpenJDK it would look like: 

/usr/lib/jvm/java-6-openjdk-amd64

This setting will be stored in your home folder: </syntaxhighlight> 

$home/.sqldeveloper/jdk

If you have problems with screen redraw in SQL Developer, then change the theme to Classic: SQL Developer -> Tools -> Preferences -> Environment -> Theme -> Classic

To connect to a MySQL database you need the JDBC Driver for MySQL (Connector/J). Download from:

Within the downloaded archive a file is contained, something like 'mysql-connector-java-5.1.18-bin.jar'.

Copy it to some logical location: 

sudo mkdir /usr/share/sqldeveloper/sqldeveloper/mysql
sudo cp mysql-connector-java-5.1.18-bin.jar /usr/share/sqldeveloper/sqldeveloper/mysql

Add the driver in SQL Developer:

  • SQL Developer -> Tools -> Database -> Third-party JDBC Drivers -> Add Entry
  • then browse to '/usr/share/sqldeveloper/sqldeveloper/mysql'
  • select 'mysql-connector-java-5.1.18-bin.jar'

You can now add a new MySQL connection in SQL Developer with servername, port, username and password.

Remove the sqldeveloper-package +- 90MB: 

sudo apt-get remove sqldeveloper-package

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/alldatabases.sql.gz

You can backup a single database with: 

mysqldump --user=root --password=**mysql-root-pwd** <database> | gzip > /path/database.sql.gz

Backup one or more named databases: 

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

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

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'.

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
    ;
Retrieved from "https://wjv.duckdns.org/mediawiki/index.php?title=MySQL&oldid=1901"