DB replicate mysql freebsd and mysqldump restore database

[golfreeze]

MySQL Slave:

Now extract mysql-4.1.12.tar.gz at different dir,

cd /opt/mysql-4.1.12

./configure --prefix=/usr/local/mysql-slave

make

make install

cd /usr/local/mysql-slave

cd bin

./mysql_install_db
(it will create a var folder )

cd ../var

cp /opt/mysql-4.1.12/support-files/my-medium.cnf my.cnf

cd ..

groupadd mysql

useradd -g mysql mysql

chown -R root .

chown -R mysql var

chgrp -R mysql .

Edit my.cnf in the var folder

[mysqld]

port = 3307

socket = /usr/local/mysql-slave/var/mysql.sock



#skip-networking



server-id = 2



# The replication master for this slave - required

master-host = localhost

master-user = slavedb

master-password = q1w2e3r4t5

master-port = 3306


Now starts the mysql server by:

cd /usr/local/mysql-slave/bin

./mysqld_safe --defaults-file=/usr/local/mysql-slave/var/my.cnf &
Configure Replication:

connect to mysql master by:

mysql --sock=/usr/local/mysql-master/mysql.sock

Create account at master for slave:

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO
slavedb@"192.168.1.27/255.255.255.0";;; identified by 'q1w2e3r4t5'; Query OK, 0
rows affected (0.28 sec)

connect to mysql slave by:

mysql --sock=/usr/local/mysql-slave/mysql.sock

mysql> slave start;
Query OK, 0 rows affected, 1 warning (0.04 sec)

Testing:

mysql connect at master:

mysql> show master status\G;

*************************** 1. row ***************************

File: adam-bin.000001

Position: 227

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.01 sec)



ERROR:

No query specified

connect mysql at slave

mysql> show slave status\G;

*************************** 1. row ***************************
Slave_IO_State: Connecting to master

Master_Host: localhost

Master_User: slavedb

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: adam-bin.000001

Read_Master_Log_Pos: 4

Relay_Log_File: adam-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: adam-bin.000001

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: 4

Relay_Log_Space: 4

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: NULL

1 row in set (0.00 sec)



ERROR:

No query specified

binlog file at masterand its position must be same in mysql slave by show slave status\G;

If every things fine, you are running a working copy of one-one master and slave relation on same
system.


Some Issues:

Replication may be fail because of number of issues. I share some of my experience with
replication.

issue: replication fails, master got down.

Master could be down due to many of the reasons. Do CHECK the FILE LIMITS IN DATABASE, SQL QUERIES
AND DISK USAGE. If any of the reason shows, fix that, restart mysqld and check master status:

mysql> show master status\G;
*************************** 1. row ***************************

adam-bin.000003

Position: 227
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.01 sec)


now check slave: by show slave status:

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: localhost
Master_User: slavedb
Master_Port: 3306
Connect_Retry: 60

Master_Log_File: adam-bin.000001

Read_Master_Log_Pos: 4

Relay_Log_File: adam-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: adam-bin.000001

Slave_IO_Running: No

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: 4
Relay_Log_Space: 4
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: NULL
1 row in set (0.00 sec)

as above values shows that master has incremented to 3rd binlog while slave is still pointed to
binlog1. so change this value by connecting at mysql slave

mysql>; stop slave;

mysql> change master to master_log_file='adam-bin.000003', master_log_pos=227;

mysql> start slave;

Now check with show slave status\G; its working fine.

Issue 2: At Slaves if duplicate error key appear
i.e
At slave

Mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.152
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: adam-bin.000048
Read_Master_Log_Pos: 317714810
Relay_Log_File: db4-relay-bin.000001
Relay_Log_Pos: 290512385
Relay_Master_Log_File: adam-bin.000048
Slave_IO_Running: Yes

Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062

Last_Error: Error 'Duplicate entry 'dekq5g820avnfdmar5qi9dkhv3' for key 1' on query.
Default database:'session_sql'. Query: 'INSERT INTO sessi ons5 VALUES
('dekq5g820avnfdmar5qi9dkhv3', UNIX_TIMESTAMP(NOW()) + 18000, 'redir ect|i:1;')'
Skip_Counter:0
Exec_Master_Log_Pos: 290512419
Relay_Log_Space: 317714776
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: NULL
1 row in set (0.00 sec)

That means that by unavailablilty of master, slave was read, or unavailability of slave, master was
updated, so a query got two primary keys found for one entry which is a mess up.
So fixing it is by:
At slave:

Mysql> set global sql_slave_skip_counter=1;

Mysql> start slave;

Mysql> show slave status\G;

[golfreeze]

เวลาคำนวณ cal mysql

#mysqladmin -u root -p extended-status

--------------------------------

20080117 Fix Mysql replicate can't sync databases
##In slave DB server
#mysql -u root -p
mysql>slave stop;
mysql>reset slave;
##In Master DB server
#mysql -u root -p
mysql>show master status;
##Write down POSITION and LOG_FILE_NAME

#mysqldump --all-database --password=xxxx > dbsc00.db
#scp dbsc00.db [email protected]:/home/golf/.
##IN slave DB server
#mysql -u root -p < dbsc00.db
mysql>change master to master_host='10.0.0.0',
master_user='slave_user',
master_password='xxx',
master_log_file='sc00-bin.000004', master_log_pos=136057;
mysql>slave start;
mysql>show slave status\G;
#It will show like this
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 207.131.5.120
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: sc00-bin.000004
Read_Master_Log_Pos: 143085
Relay_Log_File: mysql-relay-bin.000011
Relay_Log_Pos: 3088
Relay_Master_Log_File: sc00-bin.000004
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: 143085
Relay_Log_Space: 3088
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)

ERROR:
No query specified
----------
ถ้าสมมติ slave start;
แล้ว slave_SQL_Running เป็น no แล้วมี error ขึ้น

Mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.152
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: adam-bin.000048
Read_Master_Log_Pos: 317714810
Relay_Log_File: db4-relay-bin.000001
Relay_Log_Pos: 290512385
Relay_Master_Log_File: adam-bin.000048
Slave_IO_Running: Yes

Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062

Last_Error: Error 'Duplicate entry 'dekq5g820avnfdmar5qi9dkhv3' for key 1' on query.
Default database:'session_sql'. Query: 'INSERT INTO sessi ons5 VALUES
('dekq5g820avnfdmar5qi9dkhv3', UNIX_TIMESTAMP(NOW()) + 18000, 'redir ect|i:1;')'
Skip_Counter:0
Exec_Master_Log_Pos: 290512419
Relay_Log_Space: 317714776
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: NULL
1 row in set (0.00 sec)

That means that by unavailablilty of master, slave was read, or unavailability of slave, master was
updated, so a query got two primary keys found for one entry which is a mess up.
So fixing it is by:
At slave:

Mysql> set global sql_slave_skip_counter=1;

Mysql> start slave;

Mysql> show slave status\G;

[golfreeze]

We face problem on webmail server (mysql master node)
- port 3306 open but mysql socket not running and check process with command
/usr/local/etc/rc.d/mysql-server status
mysql-server not running. (effect at 2010-09-26 when /var full on webmail server) .

After check error on webmail by command
webmail# less /var/db/mysql/webmail.ntt.co.th.err

we see

100928 21:27:48 mysqld_safe mysqld from pid file /var/db/mysql/webmail.ntt.co.th.pid ended
100928 21:37:26 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
100928 21:37:27 InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Unable to lock ./ib_logfile0, error: 35
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Error in opening ./ib_logfile0
100928 21:37:27 [ERROR] Plugin 'InnoDB' init function returned error.
100928 21:37:27 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
100928 21:37:27 - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.


#####Solution
1.confirm mysql application on LDAP01
 
##Step to run mysql-replicate at ldap01 (slave node)
cd /usr/ports/database/mysql51-server
make install clean

add mysql_enable="YES" to /etc/rc.conf

#/usr/local/etc/rc.d/mysql-server restart

2.confirm horde(webmail) and mailzu can connect mysql socket on LDAP01

############ horde ##################
#vi /usr/local/horde/config/conf.php

open this comment

///////Use this when connect with mysql on ldap01 (slave)
//$conf['sql']['password'] = 'horde';
//$conf['sql']['hostspec'] = 'ldap01.mh.ntt.co.th';

then check on /var/log/horde.log on ldap01
#tail -f /var/log/horde.log

##############end horde #####################

###############begin mailzu ###################

vi /usr/local/mailzu/config/config.php

open this config

///////Use this when connect with mysql on ldap01 (slave)
//$conf['db']['hostSpec'] = '10.20.0.45:3306';

###############end mailzu ####################

3.confirm amavisd on both smtp00 and smtp01 connect mysql socket on LDAP01

#### SMTP00 #####
SMTP00# vi /usr/local/etc/amavisd.conf

set config like this

#### Mailzu connected to database ####
@lookup_sql_dsn = ( ['DBI:mysql:amavis;host=ldap01.mh.ntt.co.th;port=3306', 'amavis', 'amavis'] );


SMTP00#/usr/local/etc/rc.d/amavisd restart
then check maillog on smtp00

SMTP00#tail -f /var/log/maillog

##### SMTP00 ######


#### SMTP01 #####
SMTP01# vi /usr/local/etc/amavisd.conf

set config like this

#### Mailzu connected to database ####
@lookup_sql_dsn = ( ['DBI:mysql:amavis;host=ldap01.mh.ntt.co.th;port=3306', 'amavis', 'amavis'] );


SMTP01#/usr/local/etc/rc.d/amavisd restart
then check maillog on smtp01

SMTP01#tail -f /var/log/maillog

##### SMTP01 ######


4.move (horde) and (mailzu) configuration point to mysql on LDAP01
5.begin to fix problem on webmail.

#killall -9 mysqld
add innodb_force_recovery config line to /etc/my.cnf
#innodb_force_recovery = 4

Now we can restart the database:

/usr/local/bin/mysqld_safe &

(Note: If MySQL doesn't restart, keep increasing the innodb_force_recovery number until you get to innodb_force_recovery = Cool

Save all data into a temporary alldb.sql (this next command can take a while to finish):

mysqldump --force --compress --triggers --routines --create-options -uroot -pnttcth@946 --all-databases > /home/golf/alldb.sql

Shutdown the database again:

mysqladmin -uroot -pnttcth@946 shutdown

Delete the database directory. (Note: In my case the data was under /usr/local/var. Your setup may be different. Make sure you're deleting the correct directory)

"cd /var/db/
mv mysql mysql.ori"

Recreate the database directory and install MySQL basic tables

mkdir mysql
chown -R mysql:mysql mysql
/usr/local/bin/mysql_install_db
chown -R mysql:mysql mysql

Remove innodb_force_recovery from /etc/my.cnf and restart database:

/usr/local/bin/mysqld_safe &

Import all the data back (this next command can take a while to finish):

mysql -uroot --compress < /home/golf/alldb.sql

And finally - flush MySQL privileges (because we're also updating the MySQL table)

/usr/local/bin/mysqladmin -uroot flush-privileges


##step upgrade mysql-server on webmail from version 5.1-26 to 5.1-28
cd /usr/ports/database/mysql51-server
make deinstall clean
make install clean

###config replication mysql on webmail by add this configuration
###expire mysql-bin golf
##Replicate by golfreeze 20100928
server-id = 1
binlog-do-db=amavis
binlog-do-db=horde
log-bin=mysql-bin
expire_logs_days = 10

######

restart service mysql on webmail
#/usr/local/etc/rc.d/mysql-server restart

access to mysql on webmail
#mysql -p

mysql> show master status;
+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000071 | 283593434 | amavis,horde |                  |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)


===> This result mean mysql master node support to run replicate database (amavis) and (horde)


###Begin to config on ldap01 server (mysql slave node)


Add this config below to /etc/my.cnf

####Do replicate with Webmail (master-node) Mysql by golf 20100930
server-id=2
        replicate-do-db=amavis
        replicate-do-db=horde
log-bin=mysql-bin
relay-log=mysql-relay-bin
slave-skip-errors=all
expire_logs_days        = 10
max_binlog_size         = 100M

###restart mysql service on ldap01
#/usr/local/etc/rc.d/mysql-server restart

###Check result by

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.20.0.39
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000071
          Read_Master_Log_Pos: 290368854
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 55151318
        Relay_Master_Log_File: mysql-bin.000071
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: amavis,horde
          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: 290368854
              Relay_Log_Space: 160020749
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

ERROR:
No query specified

###################################

If you see

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: amavis,horde

that mean , ldap01 server (slave node) stil sync database with webmail server (master node)

[golfreeze]

###reference

http://www.howtoforge.com/mysql_database_replication
http://shebangme.blogspot.com/2010/02/how-to-fix-mysql-database-myisam-innodb.html

[golfreeze]

ถ้าจะทำการ dump database ผ่าน mysql console
database name = test

#mysqldump -p test > test_2011.sql

dump เสร็จทำการ restore database (service mysql ต้องรันอยู่ด้วยนะครับ)
#mysql -p test < test_2011.sql

เสร็จแล้วครับผม

[golfreeze]

ตรวจสอบเจอ Slave_SQL_Running หยุดทำงานครับ อาจจะเพราะเนื่องจากมีการ sync ข้อมูลอยู่แล้วเราไป restart service mysql พอดี
#mysql -p
show slave status;
Slave_IO_Running: Yes
Slave_SQL_Running: No


###แก้ไขโดย ให้ขยับตำแหน่งไป 1 ตำแหน่งแล้วทำการ sync ต่อไป ครับ Fixed by
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;
SHOW SLAVE STATUS\G

Slave_IO_Running: Yes
Slave_SQL_Running: Yes