Adminbuntu

Everything for the Ubuntu Server Administrator

User Tools

Site Tools


Sidebar

Server Administration


Server Applications


At the Command Line


Elsewhere


Copyright 2013 Applied Conscious Technologies, LLC

Terms of Agreement

Contact


submit to reddit

mysql

Server Applications

Ubuntu Server MySQL Administration

Ubuntu MySQL server guide: https://help.ubuntu.com/lts/serverguide/mysql.html

mysql man page: http://manpages.ubuntu.com/manpages/lucid/man1/mysql.1.html

MySQL is a powerful, fast, high capacity SQL server suitable for heavy production applications.

Backup MySQL Databases to AWS S3 Create MySQL dumps which are compressed and uploaded to Amazon S3 for storage.
Install PhpMyAdmin Install PhpMyAdmin to administer MySQL with a web browser.
Drop MySQL Tables by Partial Name Drop all tables with table names starting with a string.
Dump MySQL Tables by Partial Name Back up only tables with table names starting with a string.
Drop All Tables in a Database Drop all tables in a database without dropping the database itself.
Dump and Restore a MySQL Database Back up and restore a MySQL database from the command line.

A Word About Passwords on the Command Line

Including a MySQL password on the command line is problematic because the password is recorded in ~/.bash_history where it is possible for other users to view it, depending on permissions and whether other users have sudo rights. By default, the ~/.bash_history permissions have no read or write permissions for the group or other, only the owner's user. So by default, security is in place.

If this is the case, omit the password parameter and enter the password when prompted, after entering the command.

Run, Stop, and Restart MySQL

Control the MySQL service the standard Ubuntu way. See also: Services.

Start MySQL

sudo service mysql start

Stop MySQL

sudo service mysql stop

Restart MySQL

sudo service mysql restart

Change MySQL Root Password

mysqladmin --user=USERNAMEHERE --password=PASSWORDHERE password NEWPASSWORDHERE

Or, rerun the package configurator:

sudo dpkg-reconfigure mysql-server-5.5

MySQL Server Status

With MySQL Service Command

service mysql status

With Netstat

sudo netstat -tap | grep mysql

If MySql is running, a line of data will be returned by the above command. For example:

tcp        0      0 localhost:mysql         *:*                     LISTEN      12542/mysqld

MySQL Server Information

Show MySQL Version

mysqladmin --user=USERNAMEHERE --password=PASSWORDHERE version

Show MySQL Status

mysqladmin --user=USERNAMEHERE --password=PASSWORDHERE status

Show MySQL Extended Status

mysqladmin --user=USERNAMEHERE --password=PASSWORDHERE extended-status

Show MySQL Variables

mysqladmin --user=USERNAMEHERE --password=PASSWORDHERE variables

Show MySQL Process List

mysqladmin --user=USERNAMEHERE --password=PASSWORDHERE processlist

Get a MySQL Prompt

mysql --user=USERNAMEHERE --password=PASSWORDHERE --database=DATABASENAMEHERE

Terminal MySQL Database Commands

Show Databases

mysql --user=root --password=PASSWORDHERE -e "show databases;"

Show Tables

Show the tables in a database.

mysql --user=USERNAMEHERE --password=PASSWORDHERE --database=DATABASENAMEHERE -e "show tables;"

Show Tables with Record Count

mysql --user=USERNAMEHERE --password=PASSWORDHERE -e "SELECT Table_name,Table_rows FROM information_schema.tables WHERE TABLE_SCHEMA = 'DATABASENAMEHERE';"

Dump a Table with Date Embedded in Filename

If you need to keep a record of a table at a certain point in time.

NOW=$(date +"%Y-%m-%m");mysqldump --user=USERNAMEHERE --password=PASSWORDHERE --skip-triggers --compact --no-create-info  DATABASENAMEHERE TABLENAMEHERE > TABLENAMEHERE.$NOW.dump.sql

Truncate a Table

mysql --user=USERNAMEHERE --password=PASSWORDHERE --database=DATABASENAMEHERE -e "truncate TABLE_NAME;"

Search and Replace in a Specified Field

mysql --user=USERNAMEHERE --password=PASSWORDHERE --database=DATABASENAMEHERE -e "UPDATE TABLE_NAME SET FIELD_NAME = REPLACE(fieldname, 'replacethis', 'withthis');"

Move MySQL Data Directory

It is sometimes necessary to move the MySQL data directory to a non-standard location. For example, to a certain file system and/or to make backups more feasible.

sudo service mysql stop
sudo cp -R -p /var/lib/mysql /base/var/lib/mysql
sudo vi /etc/mysql/my.cnf
# in above, change dir in datadir line
sudo vi /etc/apparmor.d/usr.sbin.mysqld
# in above, replace lines beginning with /var/lib/mysql
sudo /etc/init.d/apparmor reload
sudo service mysql start

mysql.txt · Last modified: 2015/05/31 21:20 (external edit)