MySQL

Copying a large database from one server to another

Recently, I had to copy a large database from one server to another. I had 100G free on the source server. Unfortunately, the server would run out of memory before the mysqldump would complete. So I had to find a way to make the mysqldumps smaller. This can be done by creating separate dumps for each table. Then I had to compress the files to conserve memory. Following is the script I wrote to accomplish this.

Listing all tables and their table counts in a MySQL database

The Following SQL query will list all tables in a MySQL database and also list the row counts for each.

SELECT TABLE_NAME, TABLE_ROWS 
FROM `information_schema`.`tables` 
WHERE `table_schema` = 'mydatabase';

where mydatabase is the name or your database. The output will like something like the following:

+------------+------------+
| table_name | table_rows |
+------------+------------+
| lines      |       2271 |
| links      |        484 |
| word       |      25004 |
+------------+------------+

MySQL cheat sheet

Selecting a database:

mysql> USE database;

Listing databases:

mysql> SHOW DATABASES;

Listing tables in a db:

mysql> SHOW TABLES;

Describing the format of a table:

mysql> DESCRIBE table;

Creating a database:

mysql> CREATE DATABASE db_name;

Creating a table:

mysql> CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE)); Ex: mysql> CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE);

Resetting MySQL root password

To reset MySQL password, you need to have root access to the machine. After logging in as root, start by shutting down MySQL:

$ /sbin/service mysqld stop

Verify that all mysql processes have been shut down.

$ ps waux | grep 'mysql'

For all processes that are listed

$ kill -9

Find your mysqld_safe.

$ locate mysqld_safe

My mysqld_safe is installed at /usr/bin/mysqld_safe. Yours might be elsewhere. Start MySQL without the grant tables.

Pages