HanG321 Blog

MySQL chinese support

http://mysql.chinahtml.com/2005/1125321683.shtml

MySQL — inline edit chinese character

1. stop MySQL service
2. open my.ini
3. change default-character-set of [mysqld] and [client] as utf8
4. launch mysql
5. create table as default or select tuf8
6. enjoy ~

MySQL Master Master replication

A. background

OS: CentOS 4.4
MySQL package: 4.1.20-1.RHEL4.1
node1: 192.168.0.131   (Master 1/Slave 2)
node2: 192.168.0.132   (Master 2/Slave 1)
mysql path: /var/lib/mysql
mysql config file: /etc/my.cnf

reference:
http://www.howtoforge.com/mysql_master_master_replication
http://cha.homeip.net/blog/archives/2004/12/replication_in.html

B. procedure

1.
On Master 1, make changes in my.cnf

#vim /etc/my.cnf

  [mysqld]
  datadir=/var/lib/mysql
  socket=/var/lib/mysql/mysql.sock
  # Default to using old password format for compatibility with mysql 3.x
  # clients (those using the mysqlclient10 compatibility package).
  old_passwords=1
  
  #replication
  log-bin
  binlog-do-db=horde
  binlog-ignore-db=mysql
  binlog-ignore-db=test
  
  server-id=1
  
  [mysql.server]
  user=mysql
  basedir=/var/lib
  
  [mysqld_safe]
  err-log=/var/log/mysqld.log
  pid-file=/var/run/mysqld/mysqld.pid

2.
On master 1, create a replication slave account in mysql.

#mysql -u root -p
mysql> grant replication slave on *.* to ‘replication’@192.168.0.132 identified by ‘slave’;
mysql> exit

and restart the mysql master1.
#service mysqld restart

3.
Now edit my.cnf on Slave1 or Master2 :

  [mysqld]
  datadir=/var/lib/mysql
  socket=/var/lib/mysql/mysql.sock
  old_passwords=1
    
  server-id=2
  master-host = 192.168.0.131
  master-user = replication
  master-password = slave
  master-port = 3306
  
  [mysql.server]
  user=mysql
  basedir=/var/lib
  
  [mysqld_safe]
  err-log=/var/log/mysqld.log
  pid-file=/var/run/mysqld/mysqld.pid

4.
Restart mysql slave 1 and check config at

#service mysqld restart

#mysql -u root -p
mysql> start slave;
mysql> show slave statusG;

ensure Slave_IO_Running and Slave_SQL_Running: must be to YES.

mysql> exit

5.
On master 1:

mysql> show master status;
+————————+———-+————–+——————+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+————————+———-+————–+——————+
|MysqlMYSQL01-bin.000008 |       79 | horde        | mysql,test       |
+————————+———-+————–+——————+
1 row in set (0.00 sec)

The above scenario is for master-slave, now we will create a slave master scenario for the same systems and it will work as master master.

6.
On Master2/Slave 1, edit my.cnf and master entries into it:

#vim /etc/my.cnf

   [mysqld]
   datadir=/var/lib/mysql
   socket=/var/lib/mysql/mysql.sock
   # Default to using old password format for compatibility with mysql 3.x
   # clients (those using the mysqlclient10 compatibility package).
   old_passwords=1
   
   server-id=2
   
   master-host=192.168.0.131
   master-user=replication
   master-password=slave
   master-port=3306
   
   log-bin                     #information for becoming master added
   binlog-do-db=horde
   
   [mysql.server]
   user=mysql
   basedir=/var/lib
   
   [mysqld_safe]
   err-log=/var/log/mysqld.log
   pid-file=/var/run/mysqld/mysqld.pid

7.
Create a replication slave account on master2 for master1

mysql> grant replication slave on *.* to ‘replication’@192.168.0.131 identified by ‘slave2′;

8.
Edit my.cnf on master1 for information of its master.

#vim /etc/my.cnf

  [mysqld]
  datadir=/var/lib/mysql
  socket=/var/lib/mysql/mysql.sock
  
  # Default to using old password format for compatibility with mysql 3.x
  # clients (those using the mysqlclient10 compatibility package).
  old_passwords=1
  
  #master
  log-bin
  binlog-do-db=adam
  binlog-ignore-db=mysql
  binlog-ignore-db=test
  server-id=1
  
  #information for becoming slave.
  master-host=192.168.0.132
  master-user=replication
  master-password=slave2
  master-port=3306
  
  [mysql.server]
  user=mysql
  basedir=/var/lib

9.
Restart both mysql master1 and master2.

On mysql master1:
mysql> start slave;

On mysql master2:
mysql > show master status;

On mysql master 1:
mysql> show slave statusG;

*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.0.132
                Master_User: replication
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: Mysql1MYSQL02-bin.000008
        Read_Master_Log_Pos: 410
             Relay_Log_File: Mysql1MYSQL01-relay-bin.000008
              Relay_Log_Pos: 445
      Relay_Master_Log_File: Mysql1MYSQL02-bin.000008
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        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: 410
            Relay_Log_Space: 445
            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: 103799
1 row in set (0.00 sec)

ERROR:
No query specified

make sure its running.

Now you can create tables in the database and you will see changes in slave. Enjoy!!