From GarrettHoneycutt

MySQL

Sample my.cnf

[client]
port    = 3306
#socket  = /var/lib/mysql/mysql.sock
 
[mysqld]
ft_min_word_len=2
ft_stopword_file=/etc/my.stopwords
skip-locking
key_buffer_size = 2G
max_allowed_packet = 1G
table_cache = 1024
sort_buffer_size = 8M
read_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_stack = 393216
query_cache_size = 256M
thread_concurrency = 8
thread_cache_size = 48
set-variable = max_connections=4000
set-variable = max_connect_errors=1000000
set-variable = long_query_time=5
wait_timeout = 3600
connect_timeout = 3600
flush_time = 3600
log-slow-queries
log-error=/var/lib/mysql/mysql.err
#######################################################################
# Replication
server-id = 2
replicate-ignore-db=mysql
replicate-ignore-db=test
#######################################################################
# Binary logging
log-bin
log-slave-updates
binlog-ignore-db=mysql
binlog-ignore-db=test
auto_increment_offset= 5
auto_increment_increment= 2
set-variable = innodb_buffer_pool_size=512M
set-variable = innodb_additional_mem_pool_size=256M
 
[mysql.server]
user=mysql
basedir=/usr
datadir=/var/lib/mysql
 
[safe_mysqld]
open_files_limit=12192
 
[mysqldump]
quick
max_allowed_packet = 32M
 
[mysql]
no-auto-rehash
 
[isamchk]
key_buffer_size = 2G
sort_buffer_size = 256M
read_buffer = 4M
write_buffer = 4M

checking / repairing tables

MyISAM

The database MUST be stopped first. double check.

Check all databases

[root@host /var/lib/mysql]# for i in $(find . type -d -maxdepth 1 | grep -e ^.$ | awk -F \/ '{print $2}'); do cd $i; echo [$i]; myisamchk <insert options from below>; cd - ; done

or one by one with the following:

# cd /var/lib/mysql/[database]

# myisamchk --recover --update-state *.MYI

The output will probably go by rather quickly, you will need to look through your scrollback buffer and ensure that every table was clean. If not you want to run

# myisamchk -e --recover --update-state [broken_table].MYI

If that does not work try

# myisamchk -e --safe-recover --update-state [broken_table].MYI

repair doesn't work

If you see something like this in /var/log/daemon.log when you start mysql

Jan 22 12:08:33 db2.garretthoneycutt.com mysqld[25150]: 090122 12:08:33 [ERROR] /usr/sbin/mysqld: Table './awesomedb/table_a' is marked as crashed and last (automatic?) repair failed

Then you should look in /var/lib/mysql/awesomedb/ and see if any files are owned by root. In this case I found table_a.TMD which is a temporary file. Since it is owned by root, mysql could not do a repair table since it could not write to that file. I simply rm'd the file (OK with mysql started as long as it wasnt actively checking that table) and issued

mysql> repair TABLE table_a;

InnoDB

So you did the above MyISAM checks and when you start mysql the mysqlcheck is still taking awhile. Well, let's check the InnoDB tables.

You can tell mysql to check these on startup by adding the following to /etc/mysql/my.cnf under the [mysqld] section

innodb_force_recovery = 5

Now when you start mysql it will do a check. During this period be sure that nothing is attempting to query the database, ie: apache is stopped, you are not running mytop, or at a mysql> prompt

Here is a sample from /var/log/daemon.log when innodb_force_recovery = 4 was used. We ended up having to go to 5. Do not set to 6, keep at 5.

Feb 30 11:49:28 db2.garretthoneycutt.com mysqld_safe[24204]: started 
Feb 30 11:49:28 db2.garretthoneycutt.com mysqld[24207]:
090122 11:49:28  InnoDB: Started; log sequence number 15 595434511 
Feb 30 11:49:28 db2.garretthoneycutt.com mysqld[24207]: InnoDB: !!! innodb_force_recovery is set to 4 !!! 
Feb 30 11:49:29 db2.garretthoneycutt.com mysqld[24207]:
090122 11:49:29 [Note] /usr/sbin/mysqld: ready for connections. 
Feb 30 11:49:29 db2.garretthoneycutt.com mysqld[24207]: Version: '5.0.38-Ubuntu_0ubuntu1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Ubuntu 7.04 distribution 
Feb 30 11:49:29 db2.garretthoneycutt.com /etc/mysql/debian-start[24246]: Upgrading MySQL tables if necessary. 
Feb 30 11:49:30 db2.garretthoneycutt.com /etc/mysql/debian-start[24257]: Checking for crashed MySQL tables. 
Feb 30 11:49:47 db2.garretthoneycutt.com mysqld[24207]: Error: index `LogDate` of table `awesomedb/Log_Error` contains 1544064 entries, should be 1544065 
Feb 30 11:51:42 db2.garretthoneycutt.com mysqld[24207]: Error: index `iAccount` of table `awesomedb/Log_Login` contains 5465247 entries, should be 5465272 
Feb 30 11:52:19 db2.garretthoneycutt.com mysqld[24207]: Error: index `iAccount` of table `awesomedb/Log_SendMail` contains 3961213 entries, should be 3961216 
Feb 30 11:52:28 db2.garretthoneycutt.com /etc/mysql/debian-start[24296]: WARNING: mysqlcheck has found corrupt tables 
Feb 30 11:52:28 db2.garretthoneycutt.com /etc/mysql/debian-start[24296]: awesomedb.Log_Error 
Feb 30 11:52:28 db2.garretthoneycutt.com /etc/mysql/debian-start[24296]: error    : Corrupt 
Feb 30 11:52:28 db2.garretthoneycutt.com /etc/mysql/debian-start[24296]: awesomedb.Log_Login 
Feb 30 11:52:28 db2.garretthoneycutt.com /etc/mysql/debian-start[24296]: error    : Corrupt 
Feb 30 11:52:28 db2.garretthoneycutt.com /etc/mysql/debian-start[24296]: awesomedb.Log_SendMail 
Feb 30 11:52:28 db2.garretthoneycutt.com /etc/mysql/debian-start[24296]: error    : Corrupt 
Feb 30 11:52:28 db2.garretthoneycutt.com /etc/mysql/debian-start[24296]:  
Feb 30 11:52:28 db2.garretthoneycutt.com /etc/mysql/debian-start[24296]:  Improperly closed tables are also reported if clients are accessing 
Feb 30 11:52:28 db2.garretthoneycutt.com /etc/mysql/debian-start[24296]:  the tables *now*. A list of current connections is below. 
Feb 30 11:52:28 db2.garretthoneycutt.com /etc/mysql/debian-start[24296]:  
Feb 30 11:52:28 db2.garretthoneycutt.com /etc/mysql/debian-start[24296]: +----+------------------+-----------+----+---------+------+-------+------------------+ 
Feb 30 11:52:28 db2.garretthoneycutt.com /etc/mysql/debian-start[24296]: | Id | User             | Host      | db | Command | Time | State | Info             | 
Feb 30 11:52:28 db2.garretthoneycutt.com /etc/mysql/debian-start[24296]: +----+------------------+-----------+----+---------+------+-------+------------------+ 
Feb 30 11:52:28 db2.garretthoneycutt.com /etc/mysql/debian-start[24296]: | 15 | debian-sys-maint | localhost |    | Query   | 0    |       | show processlist | 
Feb 30 11:52:28 db2.garretthoneycutt.com /etc/mysql/debian-start[24296]: +----+------------------+-----------+----+---------+------+-------+------------------+ 
Feb 30 11:52:28 db2.garretthoneycutt.com /etc/mysql/debian-start[24296]: Uptime: 180  Threads: 1  Questions: 492  Slow queries: 0  Opens: 439  Flush tables: 1  Open tables: 321  Queries per second avg: 2.733

This should fix the tables.

DONT FORGET THIS STEP Once mysqlcheck has finished and you do not see anything else hitting /var/log/daemon.log

* stop mysql
* comment out the innodb_force_recovery = 5
* start mysql

Setup Master/Slave replication

Key

• *M:* Denotes the master
• *S:* Denotes the slave
• *foo* is the database to be replicated
• *passwd* is something secret

1. M:

   GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slavehost.garretthoneycutt.com' IDENTIFIED BY 'passwd';

2. M:

   mysql> flush PRIVILEGES;

3. M:

   mysql> flush tables with READ lock;

4. M:

   mysql> SHOW master status\G

   keep this window open, run the commands in another terminal
5. S:

   mysql> DROP DATABASE foo;

6. S:

   mysql> stop slave io_thread;

7. S:

   mysql> stop slave;

8. S:

   mysql> SHOW slave status\G

   Note that Slave_IO_Running and Slave_SQL_Running should both be No
9. M:

   # mysqldump --opt --single-transaction -p foo > /tmp/foo-`date -I`.dump.sql

10. M:

    # scp /tmp/foo-`date -I`.dump.sql user@slavehostgarretthoneycutt.com:~/

11. S:

    mysql> CREATE DATABASE foo;

12. S:

    # mysql -p foo < /home/user/foo-`date -I`.dump.sql

13. S:

mysql> CHANGE MASTER TO
MASTER_HOST='masterhost.garretthoneycutt.com',
MASTER_USER='repl',
MASTER_PASSWORD='passwd',
MASTER_LOG_FILE='recorded_log_file_name',
MASTER_LOG_POS=recorded_log_position;

SHOW master status\G

1. S:

   mysql> start slave io_thread;

2. S:

   mysql> start slave;

3. S:

   mysql> SHOW slave status\G

   Note that Slave_IO_Running and Slave_SQL_Running should both be Yes
4. M:

   mysql> unlock tables;

1. Test this by inserting data to the master and then checking to see that it was replicated to the slave.

Remove old binary logs

This is necessary or you will eventually run out of disk space

We are going to note the number of the mysql-bin file and purge up to but not including that number.

• M:

  mysql> SHOW master status\G

*************************** 1. row ***************************
            File: mysql-BIN.000010
        POSITION: 10971163
    Binlog_Do_DB: 
Binlog_Ignore_DB: mysql,test
1 row IN SET (0.00 sec)

• S:

  mysql> SHOW slave status\G

   *************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: greylist.garretthoneycutt.com
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-BIN.000009
        Read_Master_Log_Pos: 10971163
             Relay_Log_File: mysqld-relay-BIN.000002
              Relay_Log_Pos: 10970676
      Relay_Master_Log_File: mysql-BIN.000009
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: gld
        Replicate_Ignore_DB: mysql,test
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 0
                 Last_Error: 
               Skip_Counter: 0
        Exec_Master_Log_Pos: 10971163
            Relay_Log_Space: 10970676
            Until_Condition: None
             Until_Log_File: 
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File: 
         Master_SSL_CA_Path: 
            Master_SSL_Cert: 
          Master_SSL_Cipher: 
             Master_SSL_Key: 
      Seconds_Behind_Master: 0
1 row IN SET (0.00 sec)

• Note: you will notice that the earliest mention of mysql-bin is mysql-bin.000009
• M:

  mysql>  PURGE MASTER LOGS TO 'mysql-bin.000009';

• S:

  mysql>  PURGE MASTER LOGS TO 'mysql-bin.000009';

• Note: You should notice a drop in usage in file space and when you do an ls in the mysql directory you will notice those files are now gone.

Recover lost MySQL root password

Stop MySQL

# /etc/init.d/mysql stop

Wait until MySQL shuts down. Then run

# mysqld_safe --skip-grant-tables &

Then you will be able to login as root with no password.

# mysql -u root mysql

In MySQL command line prompt issue the following command:

mysql> UPDATE user SET password=PASSWORD("abcd") WHERE user="root"; 
mysql> FLUSH PRIVILEGES;

At this time your root password is reset to "abcd" and !MySQL will now know the privileges and you'll be able to login with your new password:

# mysql -u root -pabcd mysql

Determine the storage engine of a table

mysql> SHOW table status\G
*************************** 1. row ***************************
           Name: greylist
         Engine: INNODB
        Version: 10
     Row_format: Compact
           Rows: 3090741
 Avg_row_length: 914
    Data_length: 2828009472
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 AUTO_INCREMENT: NULL
    Create_time: 2007-07-11 17:52:17
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: greylist; INNODB free: 6144 kB

Cheat Sheet