?
Solved

Mysql replication with dbname

Posted on 2008-11-18
13
Medium Priority
?
599 Views
Last Modified: 2012-05-05
I have setup replication between 2 machines.  I am replicating database "test" from machine1 to machine2.

To test here is what I did

$ mysql -utest -ptest
mysql> use test;
mysql> insert into users values('myfirstname','mylastname');

The replication from machine1 to machine2 worked with the previous statement.  So my question is why doesn't the following steps replicate the data to machine2?

$ mysql -utest -ptest
mysql> use otherdatabasetest;
mysql> insert into test.users values('myfirstname','mylastname');

obviously I am selecting a different database, however I am stil inserting data into "test" database.

Thanks,

jmsloan
0
Comment
Question by:jmsloan
  • 7
  • 4
11 Comments
 
LVL 26

Expert Comment

by:Umesh
ID: 22986267
You can "use" or select the DB using "dot notation" as you did in below statement

insert into test.users values('myfirstname','mylastname');
0
 
LVL 26

Expert Comment

by:Umesh
ID: 22986329
Just wanted to say both are same...

mysql> use test;
mysql> insert into users values('myfirstname','mylastname');

and

mysql> insert into test.users values('myfirstname','mylastname');
0
 
LVL 3

Author Comment

by:jmsloan
ID: 22986983
Why isn't it working then?

0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 26

Expert Comment

by:Umesh
ID: 22987076
Pls get me both my.cnf (master, slave)

can you post the output of below commands

on slave
=========

show slave status;
0
 
LVL 3

Author Comment

by:jmsloan
ID: 22987190
MASTER my.cnf
[mysqld]
log-bin=mysql-bin
binlog-do-db=test
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
datadir=/usr/local/mysql
socket=/var/lib/mysql/mysql.sock
port=3306

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
 
[mysql.server]
user=mysql
basedir=/usr/local
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid




SLAVE my.cnf
[mysqld]
server-id=2
master-host=xxx.xxx.xxx.xxx
master-user=testuser
master-password=testpassword
master-connect-retry=60
replicate-do-db=test
datadir=/usr/local/mysql
socket=/var/lib/mysql/mysql.sock
port=3306
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
 
[mysql.server]
user=mysql
basedir=/usr/local
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid





SLAVE STATUS

| Slave_IO_State                   | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File          | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB   | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-------------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Waiting for master to send event | xxx.xxx.xxx.xxx | testuser      |        3306 |            60 | mysql-bin.000001 |               36556 | mysqld-relay-bin.000009 |           235 | mysql-bin.000001      | Yes              | Yes               | test,test |                     |                    |                        |                         |                             |          0 |            |            0 |               36556 |             235 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 |
0
 
LVL 26

Expert Comment

by:Umesh
ID: 22987249
Sorry.. pls send the output of .. I'm unable to read output of show slave status;

show slave status\G
0
 
LVL 3

Author Comment

by:jmsloan
ID: 22987275

mysql> show slave status\G;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: xxx.xxx.xxx.xxx
                Master_User: testuser
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000001
        Read_Master_Log_Pos: 36556
             Relay_Log_File: mysqld-relay-bin.000009
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000001
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: test,test
        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: 36556
            Relay_Log_Space: 235
            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)

Open in new window

0
 
LVL 26

Accepted Solution

by:
Umesh earned 500 total points
ID: 22987527
Here is the contents from MySQL manual.. ( http://dev.mysql.com/doc/refman/5.0/en/replication-options-binary-log.html )

 Tell the server to restrict binary logging to updates for which the default database is db_name (that is, the database selected by USE). All other databases that are not explicitly mentioned are ignored. If you use this option, you should ensure that you do updates only in the default database.

There is an exception to this for CREATE DATABASE, ALTER DATABASE, and DROP DATABASE statements. The server uses the database named in the statement (not the default database) to decide whether it should log the statement.

An example of what does not work as you might expect: If the server is started with binlog-do-db=sales, and you run USE prices; UPDATE sales.january SET amount=amount+1000;, this statement is not written into the binary log.

So in your case....

MASTER my.cnf
================

binlog-do-db=test

Here comes your INSERT STUFF - which is not being replicated..

mysql> use otherdatabasetest;
mysql> insert into test.users values('myfirstname','mylastname');

If we believe the MySQL manual then your insert will not be replicated...  i.e below statement is not written into the binary log.

 insert into test.users values('myfirstname','mylastname');

In that case, your replication i/o thread is unable to read it from master's binary log file.. hence it's not getting replicated...

0
 
LVL 3

Author Comment

by:jmsloan
ID: 22987746
So replication only works if you're in the default database?  No way around it?
0
 
LVL 26

Expert Comment

by:Umesh
ID: 22987824
That is what Manual says...  

0
 
LVL 26

Expert Comment

by:Umesh
ID: 22991571
There might be some chances if we can do some testing... this weekend I'll give a try and let you know..
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month14 days, 12 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question