MySQL Tips

From KdjWiki

Contents 1 Table type selection 2 Backup/Restore 3 Common Maintenance 3.1 Clear logs etc 4 Realtime Replication

Table type selection

Consider using InnoDB instead of MyISAM for read/write data.

Backup/Restore

You can dump the database into a file using:

 # mysqldump -h hostname -u user --password=password databasename > filename

You can restore the info to the database again using:

 # mysql -h hostname -u user --password=password databasename < filename

Common Maintenance

Clear logs etc

  mysql> reset master;

Realtime Replication

On the master server:

Shutdown the mysql engine:

 # mysqladmin -u root -p shutdown

Edit /etc/my.cnf and ensure that in the mysqld section the log-bin line is not commented and that the server-id line is set to 1

Example:

   ~[mysqld]
      log-bin
      server-id = 1

Connect to the mysql engine and run the following:

 GRANT REPLICATION SLAVE, SUPER, RELOAD, SELECT ON *.*
 TO ~ReplicationUser@~[slavehost.domain]
 IDENTIFIED BY '~SomePassword';

Where the ~[slavehost.domain] is the hostname and domain of the slave server.

Next, run the following:

 FLUSH TABLES WITH READ LOCK;

IN A SEPARATE SHELL do the following:

 # cd /export/opt/mysql/data
 # tar cvf /tmp/mysql-snap.tar ~[dbName]

Where ~[dbname] is the directory name, (or space separated list of names)of the databases you will be replicating.

NOTE: if you exit the mysql shell to do this, the READ LOCK will not be in effect any longer. You need to remain in the mysql shell and start a new command line session to make the tar snapshot.

In the mysql engine run:

 SHOW MASTER STATUS;

The output will look similar to this:

+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| masterhost-bin.00001 |       79 |              |                  |
+----------------------+----------+--------------+------------------+

Make note of the File, and Position, then run:

 UNLOCK TABLES;

On the slave server:

Shutdown the mysql engine:

 # mysqladmin -u root -p shutdown

Edit /etc/my.cnf and ensure that in the mysqld section the log-bin line IS commented (or not present at all) and that the server-idline is not commented and set to something other than 1

Example:

   ~[mysqld]
      #log-bin
      server-id = 2

Transfer the mysql-snap.tar file from the master host to the slave:

 # scp someuser@master_host:/tmp/mysql-snap.tar /tmp/

In the mysql data directory, untar the mysql snapshot:

 # cd /export/opt/mysql/data
 # tar xvf /tmp/mysql-snap.tar

Start the mysql server:

 # /etc/init.d/mysql start

Connect to the mysql engine and run the following:

 CHANGE MASTER TO
 MASTER_HOST='master_host_name',
 MASTER_USER='replication_user_name',
 MASTER_PASSWORD='replication_password',
 MASTER_LOG_FILE='recorded_log_file_name',
 MASTER_LOG_POS=recorded_log_position;

Where master_host_name is the master host name, and the replication_user_name/replication_password are the username/password set up for replication on the master server.

The recorded_log_file_name and recorded_log_position should be the values you obtained by running SHOW MASTER STATUS on the master host.

Example:

 CHANGE MASTER TO
 MASTER_HOST='masterhost.domain.com',
 MASTER_USER='~ReplicationUser',
 MASTER_PASSWORD='~SomePassword',
 MASTER_LOG_FILE='masterhost-bin.00001',
 MASTER_LOG_POS=79;

Next, run:

 START SLAVE;

That's all there is to it, in theory.