Category Archives: MySQL

MySQL: How do you use symlinks with MySQL tables?

15 Replies

Creating table symlinks in MySQL is very easy and it is probably one of the features a lot of MySQL users overlook or never think about. You may ask, why would I want to use symlinks? Some of the reasons you would want symlinks is if you are running low on disk space on the partition where your data generally is and/or you want to move one (or more) table(s) on to a different disk/partition for performance reasons.

One of the things worth mentioning is that MySQL documentation states: “Symlinks are fully supported only for MyISAM tables. For files used by tables for other storage engines, you may get strange problems if you try to use symbolic links.” Keeping that in mind, if you have a innodb table and would like to create symlinks for, you should change the engine type to myisam before symlinking. Although, I have heard and personally used symlinks for innodb databases with no side affects.

First let us find out if your MySQL configuration allows symlinks:

mysql> SHOW VARIABLES LIKE 'have_symlink'\G
*************************** 1. row ***************************
Variable_name: have_symlink
Value: YES

I am going to make following assumptions:

  • MySQL installation is in /usr/local/mysql.
  • You want to create new tables under /mnt/another_partition/mysql/var
    1. you must create the dir: mkdir /mnt/another_partition/mysql/var -p
    2. Make it owned by mysql user: chown mysql /mnt/another_partition/mysql/var

We will create some test data. Following are the steps:

mysql> create database db1;
Query OK, 1 row affected (0.05 sec)
mysql> use db1;
Database changed
mysql> CREATE TABLE `users` ( `id` int(11) default NULL, `name` varchar(255) default NULL ) ENGINE=myisam, data directory="/mnt/another_partition/mysql/var", index directory="/mnt/another_partition/mysql/var";
Query OK, 0 rows affected (0.09 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| users |
+---------------+

Drop back to command prompt and “ls” your db1 directory as shown below. Here you can see that users table’s data file (users.MYD) is a symbolic link to the location we specified. You can see that users index file (users.MYI) is symlinked to the location we specified as well.

# ls -al /usr/local/mysql/var/db1/
total 24K
drwx------ 2 mysql mysql 4.0K May 2 00:03 ./
drwx------ 23 mysql mysql 4.0K May 1 23:59 ../
-rw-rw---- 1 mysql mysql 65 May 1 09:33 db.opt
-rw-rw---- 1 mysql mysql 8.4K May 2 00:03 users.frm
lrwxrwxrwx 1 mysql mysql 42 May 2 00:03 users.MYD -> /mnt/another_partition/mysql/var/users.MYD
lrwxrwxrwx 1 mysql mysql 42 May 2 00:03 users.MYI -> /mnt/another_partition/mysql/var/users.MYI

Here are the real files which above symlinks are pointing to:

# ls -al /mnt/another_partition/mysql/var/
total 12K
drwxr-xr-x 2 mysql root 4.0K May 2 00:03 ./
drwxr-xr-x 3 root root 4.0K May 1 23:39 ../
-rw-rw---- 1 mysql mysql 0 May 2 00:03 users.MYD
-rw-rw---- 1 mysql mysql 1.0K May 2 00:03 users.MYI

If you go back in to mysql prompt and insert couple rows, you can see the size of users.MYD increase:

mysql> insert into db1.users values (1,"test1");
Query OK, 1 row affected (0.01 sec)
mysql> insert into db1.users values (2,"test2");
Query OK, 1 row affected (0.01 sec)
# ls -al /mnt/another_partition/mysql/var/
total 16K
drwxr-xr-x 2 mysql root 4.0K May 2 00:03 ./
drwxr-xr-x 3 root root 4.0K May 1 23:39 ../
-rw-rw---- 1 mysql mysql 40 May 2 00:08 users.MYD
-rw-rw---- 1 mysql mysql 1.0K May 2 00:08 users.MYI

If you already have data file and index file you would like to move, I recommend shutting down your MySQL server and moving the files manually and create symbolic links by using “ln -s” command. You can read more about ln by doing man ls

NOTE: Even though MySQL documentation says this is only supported 100% for myisam tables, I have read few posts where people say they have done this with innodb tables without any problems. If anybody else can comment on this, I would greatly appreciate it.

————————————-
DISCLAIMER: Please be smart and use code found on internet carefully. Make backups often. And yeah.. last but not least.. I am not responsible for any damage caused by this posting. Use at your own risk.

MySQL Replication Series (tip #1): what should be replicated and what should not be replicated?

17 Replies

Welcome to Tip #1 in MySQL Replication Series. In this tip we will go over what to do when you only want to replicate certain data to slave(s). Most general way to tell what is replicated to which slave is to include following configuration directive in my.cnf file depending on your environment and your goals. We will start with slave server side options since you have more flexibility on slave on what to replicate and what not to.

Slave server options:

  • replicate-do-db = dbname (or) replicate-do-db = dbname1, dbname2, …, dbnameN
    This option is used on slave server to tell the server to only replicate dbname db on this particular host. You would want to use this if you have a master which is replicating to multiple slaves and each slave may contain different database for read performance reasons.
  • replicate-ignore-db = dbname (or) replicate-ignore-db = dbname1, dbname2, …, dbnameN
    This option is used on slave server to tell server to ignore database(s) listed. This is used when you want to replicate every database except certain individual ones. For example, you may want to replicate-ignore-db=testdb
  • replicate-do-table = dbname.tablename
    This specifies a table from a database to be replicated.
  • replicate-ignore-table = dbname.tablename
    This is very useful and often ignored. If you have logging table which you only do writes to but never read from, there is no real point to replicate that table to slave(s). This way you ignore specific tables.
  • replicate-wild-do-table=dbname.tablename%
    This is another option which can prove itself to be very useful. Let’s say you have database with multiple type of open source installations (phpbb, wordpress, drupal, etc), and you want to designate slave(s) to only be used for phpbb, you would do: replicate-wild-do-table=dbname.phpbb%
  • replicate-wild-ignore-table=dbname.tablename%
    Another option which can be used instead of above example. Say you wanted everything on this particular slave BUT phpbb tables. You would put this in your config: replicate-wild-ignore-table=dbname.phpbb%

NOTE:  for wildcards, you can use generic mysql wildcards.

Master server options:

  • binlog-do-db = dbname (or) binlog-do-db = dbname1, dbname2, …, dbnameN
    This option is used on master server to tell the master server to only log queries for dbname db. This is helpful if your master has multiple databases but you only want to replicate selected few to all slaves.
  • binlog-ignore-db = dbname (or) binlog-ignore-db = dbname1, dbname2, …, dbnameN
    Ignore specified database and do not log queries referring to list database(s).

In order for mysql to know which database queries to log (or not to log) you have to make sure you specify what is your default db by doing mysql_select_db() or issuing “use dbname” command. For example, if you had binlog-do-db=forums and you issue a query: delete from main.users (basically saying, delete everything from users table in main database), it will still log the command even though main database is not in the binlog-do-db list. To get around this, you should do the following: use main; delete from users; If you do not do that, your query “delete from main.users” will be logged and executed on slave(s). There is a high probability that you do not have a test database on your slave.

NOTE: This post is part of series on MySQL Replication. Here is the original post: MySQL Replication Series: How does MySQL replication work?

————————————-
DISCLAIMER: Please be smart and use code found on internet carefully. Make backups often. And yeah.. last but not least.. I am not responsible for any damage caused by this posting. Use at your own risk.

MySQL Replication Series: How does MySQL replication work?

2 Replies

Setting up replication is not hard and can be done by following simple directions. You can learn setting up two type of replication MySQL offers here: master-master replication and master-slave replication. Once you have replication set up, you can start playing with it without doing any more changes to the configuration. But let us face it, using replication in production environment and for sites which are either going live or are live, requires deeper knowledge of how replication can be used and what it can (or can not) do. We will start this “MySQL Replication Series” with briefly going over how MySQL replication works. Later in the series I will be posting some tips on how to fine tune the settings to get replication to do what you want it to do. I will be keeping all the tips separate and will add links to all of the tips at the bottom of this post as I post them. Let us start by quick overview of how replication works.

In a MySQL replication setup master server puts all the commands it is executing which affects data (insert, delete, update, truncate, etc) into a file referred to as bin-log file. Slave(s) than pick up the big-log files and execute those commands on themselves. You can see which bin file is currently being used by typing: show master status, you would see output similar to:

mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 519132576

This tells us that right now we are writing to mysql-bin.000001 and it is at position 519132576. Why does this position matter? It matters if you are looking at your slaves and trying to figure out where it is currently as far as position goes. If you were to do mysqldump and do it with master info, it is this number which gets added to the dump file so when you import on slave, it knows where to start from.

At this point in time if you go to slave, and type: show slave status\G, you would see something similiar to below:

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.1
Master_User: slaveuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 519132576

As you can see it is at the same point meaning it is all caught up to the master (this snapshot was taken from a site which was not accepting traffic at the time). Another thing you can look at, even though it is not the most accurate way to measure how behind you are, is to check last row in “show slave status” labeled: Seconds_Behind_Master: 0 <- if this is greater than 0, than you are lagging behind the master. Which in fact just means that it has not played back some of the queries. If it is NULL, it means that your slave is either not started or it has errors which needs attention. You can start and stop the slave by typing: start slave; and stop slave; respectively.

Hopefully this gives an overview of how MySQL replication works. As always, please feel free to correct me, add on to what I have said or just make comments. I am, like many others, learning new things every day.

Tip #1: What should be replicated and what should not be replicated?
————————————-
DISCLAIMER: Please be smart and use code found on internet carefully. Make backups often. And yeah.. last but not least.. I am not responsible for any damage caused by this posting. Use at your own risk.

MySQL: Solution for ERROR 1442 (HY000): Can’t update table ‘t1’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

54 Replies

Here is a sample table you can create to test following problem/solution on:

CREATE TABLE `t1` (
`a` char(1) default NULL,
`b` smallint(6) default NULL
);
insert into t1 values ('y','1');

I have a table t1 which has column a and b, I want column a to be updated to ‘n’ when column b = 0. Here is the first version I created:

DELIMITER |
CREATE TRIGGER trigger1 AFTER UPDATE ON t1
FOR EACH ROW UPDATE t1 SET a= 'n' WHERE b=0;
|
DELIMITER ;

The trigger created successfully but I got this error when I tried to do an update on column b on table t1:
mysql> update t1 set b=0;
ERROR 1442 (HY000): Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

After searching online for a while and trying different solutions, I finally found a way to update the table which has trigger on it:

drop trigger trigger1;
DELIMITER |
CREATE TRIGGER trigger1 BEFORE UPDATE ON t1
FOR EACH ROW
BEGIN
IF NEW.b=0 THEN
SET NEW.a = 'n';
END IF;
END
|
DELIMITER ;

After the new trigger is in, I issued the same update query and “ERROR 1442 (HY000): Can’t update table ‘t1’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.” didn’t show up and it updated the col a value to “n” as it suppose to.

mysql> update t1 set b=0;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1\G
*************************** 1. row ***************************
a: n
b: 0

Therefore, if you want to create a trigger on the table which will update itself, make sure you use the NEW.column_name to refer to the row after it’s updated and don’t do the full update statement!

However, if you are updating some other table, then you can use the regular update statement:

DELIMITER |
CREATE TRIGGER trigger1 AFTER UPDATE ON t1
FOR EACH ROW UPDATE t2 SET a= 'n' WHERE b=0;
|
DELIMITER ;

————————————-
DISCLAIMER: Please be smart and use code found on internet carefully. Make backups often. And yeah.. last but not least.. I am not responsible for any damage caused by this posting. Use at your own risk.

MySQL: How do you set up master-master replication in MySQL? (CentOS, RHEL, Fedora)

16 Replies

Setting up master-master replication in MySQL is very similar to how we set up master/slave replication. You can read up about how to setup master/slave replication in my previous post: How to set up master/slave replication in MySQL. There is obviously pros and cons about using master/master replication. But this is not a post which discuses advantages and disadvantages for using master/master replication. One of the differences between master/master set up and master/slave is that in master/master set up, you have true redundancy. If one server dies, second server can take all the inserts/selects. In master/slave setup, if master dies, you will have to go through steps to make slave become the master. Master/master set up we are going to set up is essentially master/slave and slave/master. Meaning, if you had two servers, db0 and db1, your setup will be db0(master)/db1(slave) and also db0(slave)/db1(master). Here are some assumptions:

Master1 server ip: 10.0.0.1
Master2 server ip: 10.0.0.2
Slave username: slaveuser
Slave pw: slavepw
Your data directory is: /usr/local/mysql/var/

Let us go through the steps you must take on Master1 to enable it to act as master and slave by using following configuration which goes under [mysqld] section:

# let's make it so auto increment columns behave by having different increments on both servers
auto_increment_increment=2
auto_increment_offset=1
# Replication Master Server
# binary logging is required for replication
log-bin=master1-bin
binlog-ignore-db=mysql
binlog-ignore-db=test
# required unique id between 1 and 2^32 - 1
server-id = 1
#following is the slave settings so this server can connect to master2
master-host = 10.0.0.2
master-user = slaveuser
master-password = slavepw
master-port = 3306

Following is the configuration which goes on master2 under [mysqld] section:

# let's make it so auto increment columns behave by having different increments on both servers
auto_increment_increment=2
auto_increment_offset=2
# Replication Master Server
# binary logging is required for replication
log-bin=master2-bin
binlog-ignore-db=mysql
binlog-ignore-db=test
# required unique id between 1 and 2^32 - 1
server-id = 2
#following is the slave settings so this server can connect to master2
master-host = 10.0.0.1
master-user = slaveuser
master-password = slavepw
master-port = 3306

On master1 server, go to mysql> prompt and add the appropriate user:

mysql> grant replication slave on *.* to slaveuser@'10.0.0.2' identified by 'slavepw';

On master2 server do the same but allow right ip:

mysql> grant replication slave on *.* to slaveuser@'10.0.0.1' identified by 'slavepw';

Restart both of the master servers and check slave status:

mysql> show slave status\G

That is all you have to do to set up the replication. Of course there are a lot more configuration options but this should get your replication going and you can tweak from here on.

————————————-
DISCLAIMER: Please be smart and use code found on internet carefully. Make backups often. And yeah.. last but not least.. I am not responsible for any damage caused by this posting. Use at your own risk.