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.

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.

Binding

You can set up binding for your MySQL server, meaning it will be accessible to the outside world. Note that before you do this, your password should be very strong, otherwise others can hack your MySQL database.

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

References

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