MySQL | Linux Ask!

Unsafe functions for MySQL’s statement based replication

Feb 042011

Unsafe SQL for MySQL's statement based replication

Answer:

Following are the list of MySQL's function which are unsafe if you are using statement based replication (which is the default in MySQL 5)

In short, if you are using MySQL's replication, do not use the following functions.

LOAD_FILE()
UUID(), UUID_SHORT()
USER()
FOUND_ROWS()
SYSDATE()
GET_LOCK()
IS_FREE_LOCK()
IS_USED_LOCK()
MASTER_POS_WAIT()
RELEASE_LOCK()
SLEEP()
VERSION()

Reference: http://dev.mysql.com/doc/refman/5.1/en/replication-sbr-rbr.html

How to repair a broken MySQL replication?

MySQL No Responses »

Jan 272011

How to repair a broken MySQL replication?

Answer:

Sometimes you might discovered the slave MySQL server is having problem to replicate data from the master, when running the "SHOW SLAVE STATUS" command, it show:

mysql> SHOW SLAVE STATUS \G
..
          Slave_IO_Running: Yes
          Slave_SQL_Running: No
..
          Last_Errno: 1146
          Last_Error: Error 'Table 'db2.table3' doesn't exist' on query. Default database: 'db1'. 
..

The above error message said the table "db2.table3" does not exist in the slave DB. In order to fix this error, we just simply ignore this error and resume the replication. To do so:

1. Stop the slave from replication
mysql> STOP SLAVE;

2. Skip the error
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

3. Resume the replication
mysql> STARTSLAVE;

You will now check again the status using "SHOW SLAVE STATUS", and you will discover "Slave_SQL_Running: Yes" this time. Repeat the above steps if needed.

Import a CSV file into MySQL database

MySQL 1 Response »

Jan 092011

Import a CSV file into MySQL database

Answer:

To import a CSV file into the MySQL database, you can use the mysqlimport command.

# mysqlimport mytable.csv test.mytable

The above command import the CSV file into the table named `mytable` in the database `test`

Enable MySQL binary log (bin log)

MySQL 1 Response »

Dec 292010

Enable MySQL binary log (bin log)

Answer:

The MySQL binary log contains the changes to the database in binary log format. They are useful for

Database recovery
Replication

To enable it, edit the MySQL configurations, e.g. /etc/my.cnf

..
log-bin
..

Don't forget to restart MySQL to take effect.

# /sbin/service mysqld restart

Initialize MySQL Data Directory

MySQL No Responses »

Nov 222010

Initialize MySQL Data Directory

Answer:

When you compiled a new MySQL server, it is needed to create a default data directory.

To do so, you need to use the mysql_install_db command.

# bin/mysql_install_db --user=mysql \
    --basedir=/opt/mysql/mysql \
    --datadir=/opt/mysql/mysql/data

The --datadir is the target directory for storing the database files, where --basedir specifies the MySQL server base directory.

Reference: http://dev.mysql.com/doc/refman/5.0/en/mysql-install-db.html

Older Entries Newer Entries