mysql

Sunday, May 04, 2008

start mysql

# /usr/local/mysql/bin/mysqld_safe &

shutdown mysql

# /usr/local/mysql/bin/mysqladmin -uusername -ppassword shutdown

Tuesday, December 05, 2006

mysqldump a database except for certain tables

[root@someserver tomer]# /usr/local/mysql/bin/mysqldump -uroot -psomepassword --ignore-table=databasename.tablename databasename > /tmp/thisisthedump.sql

Tuesday, October 03, 2006

Change column size in mysql

alter table TABLE_NAME modify column COLUMN_NAME varchar(xxx);

Tuesday, September 26, 2006

Change varchar column size

ALTER TABLE tablename MODIFY columnname varchar(1000);

Monday, August 07, 2006

Automatic purge of slave binlogs replication

In the crontab I put the following:

30 2 * * 0,2,4,6 /usr/local/axisutilities/removelogs.sh

My script is like this:

#!/bin/bash
/usr/local/mysql/bin/mysql -paxismobile < /usr/local/axisutilities/deletelogs.sql
Which calls the following file.

Deletelogs.sql:

PURGE MASTER LOGS BEFORE now() -interval 24 hour;
PURGE BINARY LOGS BEFORE now() -interval 24 hour;
exit

Wednesday, July 12, 2006

Live Backups of MySQL Using Replication

One of the difficulties with a large and active MySQL database is making clean backups without having to bring the server down. Otherwise, a backup may slow down the system and there may be inconsistency with data, since related tables may be changed while another is being backed up. Taking the server down will ensure consistency of data, but it means interruption of service to users. Sometimes this is necessary and unavoidable, but daily server outages for backing up data may be unacceptable. A simple alternative method to ensure reliable backups without having to shut down the server daily is to set up replication for MySQL.

http://www.onlamp.com/pub/a/onlamp/2005/06/16/MySQLian.html

Sunday, July 02, 2006

SQL result -> File

There is a nice article about how to issue an sql and put the result in a file.
Basically its something like:

mysql> select emp_id, emp_name from emps into outfile 'c:/test.txt';
Query OK, 4 rows affected (0.03 sec)

http://gilfster.blogspot.com/2005/11/writing-sql-results-to-file.html