cordump.com

install via CPAN DBI & DBD::mysql & Time::HiRes
CPAN install fails for Term::ReadKey & Term::ANSIColor so install via fink
then hack mytop to
use lib ‘/sw/lib/perl5/5.8.8/darwin-thread-multi-2level’;
then run
mytop -u username -p passwd -h hostname -d any real DB on the host


Mysql> show status;

This will show about 245 data points in Mysql 5 for the current session, throw GLOBAL in to view the status for all sessions.

Mysql> show GLOBAL status;

You can limit the results to a specific data point or subset of data using like

mysql> show global status like ‘thread%’;

So to see how many active connections you have do

Mysql> show status like ‘threads_connected%’;

You can get the list of the various data points and what the mean in mysql 5 here.

Just edit the appropriate lines in the script and run it from the crontab on your Mysql Master Server.
This will optimize every table in every database on the local machine then send an email detailing which database/tables it optimized with timestamps so you can gauge how long each table took
A word of warning when using optimize it will write-lock the table it is currently working on and it could take several hours for a large table to be optimized. This should only be run when DOWNTIME can be tolerated as most applications will hang waiting for the write lock to disappear.
Read more about optimize Here to determine if the risk/rewards is worth it for you

(continue reading…)

When using MySQL replication, if an error occurs during replication to the slave all updates to that slave are halted. An administrator must intervene and resolve the root issue or using the SET GLOBAL SQL_SLAVE_SKIP_COUNTER command to skip the error like the following.

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1000;
mysql> start slave;

However, there occasionally comes a time when you just want MySQL replication to continue, ignoring any errors. If you find yourself think about doing this, first consider if you should seek psychiatric help, then if you still wish to proceed you can add the following to your /etc/my.cnf

slave-skip-errors=[err_no]

Where [err_no] equals the error number you have be receiving when replication halts. Or if you do not want replication to stop for any reason use the keyword “all” in place of [err_no]. Using either of these will pretty much negate the value of the slave for anything besides the worst case disaster recovery since it will no longer be a data consistent mirror.
That is my quick lesson in how to keep replication going even at the cost of severely degraded backups. The Official MySQL documentation is here, near the bottom just before the user comments.

This week at $Employer we are rolling out our new Enterprise SAN and too make it a little easier we are doing the migration in the following manner.

• Build a new MySQL Server (mysql-server-3)
  
• Clone the existing MySQL replicant (mysql-server-2) to the new server (mysql-server-3)
  
• Ensure that mysql-server-3 is a functional slave of the MySQL primary (mysql-server-1)
  
• Rebuild the old replicant server (mysql-server-2) we are also taking advantage to upgrade to CentOS 4.4
  
• Clone mysql-server-3 back to mysql-server-2
  
• Ensure that mysql-server-2 is a functional slave of the MySQL primary (mysql-server-1)
  
• Make mysql-server-3 a slave of mysql-server-2 now
  

After all that the replication looks like this
mysql-server-1 –replication–> mysql-server-2 –replication–> mysql-server-3
Then we point all the MySQL clients at mysql-server-2 and re-use mysql-server-1 for other purposes.
Now that you understand my madness let me get to the point. In order to accomplish this we are doing a lot of cloning of the mysql slaves, and this is how we went about bringing one of the slaves (mysql-server-3) online.

1. Shutdown MySQL on the existing mysql slave (mysql-server-2)
   
2. Copy the MySQL data directory to the new host including all bin files.

   $ rsync -ave ssh –progress /var/lib/mysql mysql-server-3:/var/lib

3. Start up MySQL on the existing slave (mysql-server-2)
   
4. On the new slave (mysql-server-3) remove the relay-log.info file. Remember safety first, I recommend renaming it to OLD_relay-log.info instead of deleting it.
   
5. Next take a peek at the master.info file, here is the information you need to setup replication.

   14
   
   mysql-server-1-bin.000333 <-- MASTER_LOG_FILE
   
   10179085 <-- MASTER_LOG_POS
   
   mysql-server-1 <-- MASTER_HOST
   
   MYSQL_SLAVE_USER
   
   MYSQL_SLAVE_PASS
   
   MYSQL_PORT
   
   MYSQL_TIMEOUT
   
   0
   

6. Start up MySQL on the new slave (mysql-server-3) and connect to the instance

   $ mysql -u MYSQL_USER -p

7. Stop the slave process

   mysql> stop slave;

8. Setup replication

   mysql> change master to MASTER_HOST = ‘mysql-server-1′, MASTER_USER = ‘MYSQL_SLAVE_USER’, MASTER_PASSWORD = ‘MYSQL_SLAVE_PASSWD’, MASTER_LOG_FILE = ‘mysql-server-1-bin.000333′ , MASTER_LOG_POS = 10179085;

9. Start the slave process

   mysql> stop slave;

10. Then check the slave the status to see how its doing.

    mysql> show slave status \G
    
    *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event
    
    Master_Host: mysql-server-1
    
    Master_User: MYSQL_SLAVE_USER
    
    Master_Port: 3306
    
    Connect_Retry: 60
    
    Master_Log_File: mysql-server-1-bin.000333
    
    Read_Master_Log_Pos: 10179085
    
    Relay_Log_File: mysql-server-3-relay-bin.000005
    
    Relay_Log_Pos: 7615644
    
    Relay_Master_Log_File: mysql-server-1-bin.000333
    
    Slave_IO_Running: Yes
    
    Slave_SQL_Running: Yes
    
    Replicate_Do_DB:
    
    Replicate_Ignore_DB:
    
    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: 10179085
    
    Relay_Log_Space: 761564479
    
    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)
    

    Now if it looked something like this you are all done, otherwise check the FQDN.err log, it is generally pretty helpful in resolving issues.