A blog by a system administrator and programmer.

Simple MySQL Replication HOWTO 
Thursday, November 1, 2007, 06:10 PM - System
Posted by Freddy Chu

Requirements:



* Slave server should be newer version than the master server. It is adviced that both servers are in same version
* Network port 3306 of both server should be avaliable
* log-bin is enabled


Procedure



1. edit my.cnf to add log-bin and server-id, server-id should be unique and from 1 to 2^32 - 1.
[mysqld]
server-id = 1
log-bin


2. restart the mysql server both server if you have do many modification in step 1

3. create replicate user, if you are using MySQL before 4.0.2 replace "REPLICATION SLAVE" by "FILE"
GRANT REPLICATION SLAVE ON *.* TO 'Username'@'Host' IDENTIFIED BY 'password';


4. lock all tables on master server

mysql> FLUSH TABLES WITH READ LOCK;


5. Copy all mysql files to the slave server

6. At master server run show master status, remember the file and position info

mysql> SHOW MASTER STATUS;
+--------------+----------+--------------+------------------+
| File | Position | Binlog_do_db | Binlog_ignore_db |
+--------------+----------+--------------+------------------+
| log-bin.002 | 189 | | |
+--------------+----------+--------------+------------------+


7. At slave server run change master to use the account create in step 3 and info in step 6
CHANGE MASTER TO
MASTER_HOST='master_server_ip',
MASTER_USER='slave_user_name',
MASTER_PASSWORD='slave_password',
MASTER_LOG_FILE='file_in_step_7',MASTER_LOG_POS=position_in_step_7;


8. run Slave start

9. unlock tables on master server

mysql>UNLOCK TABLES;



Now you will have a replicate MySQL server, just test to modify any records. :D
9 comments ( 91 views )   |  permalink   |   ( 3 / 2425 )
Mysql problems 
Saturday, October 27, 2007, 06:05 PM - System
Posted by Freddy Chu
Get Error 1033 "Incorrect information in file xxxx.frm" when run REPAIR

* Found a backup frm file if you can and overwrite the existing one
* If you do not have frm backup, you may need to find the original "create table" statement

1. Backup those table's frm, MYI, MYD first
2. use the original create table statement to create a new table with another name
3. overwrite the table's frm with the new table's frm
4. run REPAIR TABLE again



mysqldump: Got error: 1105: not found (Errcode: 24) when using LOCK TABLES

* this happens usually caused by open_files_limit too low
* edit my.cnf at [mysqld]" part

[mysqld]
open_files_limit = 8192


* restart mysql server
9 comments ( 77 views )   |  permalink   |   ( 3 / 2385 )

<<First <Back | 1 | 2 |