asked on

Mysql replication with dbname

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

Umesh

You can "use" or select the DB using "dot notation" as you did in below statement

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

Umesh

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');

jmsloan

ASKER

Why isn't it working then?

Umesh

Pls get me both my.cnf (master, slave)

can you post the output of below commands

on slave
=========

show slave status;

jmsloan

ASKER

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 |

Umesh

Sorry.. pls send the output of .. I'm unable to read output of show slave status;

show slave status\G

jmsloan

ASKER

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

ASKER CERTIFIED SOLUTION

Umesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jmsloan

ASKER

So replication only works if you're in the default database? No way around it?

Umesh

That is what Manual says...

Umesh

There might be some chances if we can do some testing... this weekend I'll give a try and let you know..