MySQL

Installing

You should probably install the binary version, or the version provided by your OS's package manager, because installing MySQL from source code can be problematic.

Backups

Backing Up MySQL Databases

You can back up a particular database by running the command:

$ mysqldump -u root -pYOURPASSWORDHERE DB_NAME_HERE > backup.sql

If you want to back up all databases, include the --all-databases flag:

$ mysqldump -u root -pYOURPASSWORDHERE --all-databases > backup.sql

Restoring Backed Up MySQL Databases

To restore a backed-up database,

$ mysql -u root -pYOURPASSWORDHERE DB_NAME_HERE < backup.sql

To restore a backed-up set of databases (e.g. if you used the --all-databases flag when using mysqldump), don't specify the name of the database:

$ mysql -u root -pYOURPASSWORDHERE < backup.sql

Populating a Database From Another Database

There is a command that will allow you to connect two databases together to populate one database from another. If you are on the local host that contains an EMPTY database, and you want to populate the local host's MySQL databases from a remote host, you can use the following command:

$ mysqldump --opt db_name | mysql --host=remote_host -C db_name

Note that this requires the remote host's MySQL to have a bind address, meaning, it should be bound to an IP address instead of the localhost. Otherwise it won't be accessible to the outside world.

MediaWiki

In case you did not already think of it, this provides an extremely simple and handy way to backup your MediaWiki server. You can use mysqldump to output the entire contents of your wiki (minus extensions and skins/stylesheets) to a single SQL file. This will be modest in size, typically a few MB or (in cases of extremely large wikis) maybe 100 MB.

One really cool way to test this out and make sure it works as expected is to dump your MediaWiki databases, then load it up into a MySQL database on a virtual computer using software like VirtualBox. You can load the database before or after you create/set up MediaWiki - if MediaWiki tries to set up a database and finds one already there, it will just use that one. That means that if you load the MySQL database, then install MediaWiki, your articles will all show up intact.

phpMyAdmin

You can also backup your MySQL databases from phpMyAdmin, which is a handy piece of software that provides a nice GUI interface to dealing with your MySQL databases.

MySQL Server

Configuration File

Using a Linux-installed MySQL will put the configuration file in /etc/mysql/my.cnf. Check your version's documentation for more information if you can't find it.

Bind to IP

Binding to an IP address means the server is accessible to the outside world through that interface and IP address only. If you bind MySQL to a VPN IP address, only other computers on the VPN will be able to access the server.

In your MySQL configuration file (probably called my.cnf), change the line:

bind-address     = localhost

to:

bind-address     = A.B.C.D

(where A.B.C.D is the IP address of the computer with the MySQL database). This assumes the computer is not behind a firewall, and is accessible (via the IP address given to bind-address) to the outside world. The default port number is 3306, so if A.B.C.D:3306 is not accessible to the outside world, you'll have to change stuff.

You can test whether A.B.C.D:3306 is accessible to the outside world by using telnet:

telnet A.B.C.D 3306

Port Number

You can specify the port number by changing the port argument in your configuration file:

port        = 3306

Basic Operations

Get a Local MySQL Shell

If you log in without specifying any connection parameters, like so:

$ mysql --user=root --password

this will, by default, connect to the host localhost. If no database is specified, it will not connect to a default database.

Get a remote MySQL shell

If our MySQL server is running on a remote host like 1.2.3.4, we can specify the host using the --host or -h flags:

$ mysql --host=1.2.3.4 --user=root --password

If it is running on port 6666, we can specify this using the --port or -P flags:

$ mysql --host=1.2.3.4 --port=6666 --user=root --password

Other options include the protocol to use, specifying a host with IPv6 connections, etc.

Link to MySQL documentation on topic of connecting to MySQL servers: https://dev.mysql.com/doc/refman/5.7/en/connecting.html

Show running threads and connections

To show running threads and connections, use the command:

mysql> show processlist;

Generate list of users

To list all users in the database:

mysql> SELECT User, Host, Password FROM mysql.user;

References

• MySQL documentation for mysqldump: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html