Follow

Scenario :  Setup mysql master slave replication over ssl

Master  Server  :  192.168.1.1   

Slave  Server    :  192.168.1.2

Confirm your mysql server is compiled/enabled to support ssl connections using the following command

# mysql -u root -p

mysql >   show variables like ‘%ssl%’ ;

If you are getting an output some thing like as follows then you can confirm mysql is compiled to support ssl connections

mysql> show variables like ‘%ssl%’;

| have_openssl  | DISABLED |

| have_ssl            | DISABLED |

The above shows that mysql is compiled with ssl support but it not enabled in the configuration .

Create Certificates

# cd /var/lib/mysql

# mkdir ssl

>>> Create CA Certificate

# openssl genrsa 2048 > ca-key.pem

# openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

>>> Create Server Certificate

# openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem

# openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

>>> Create Client Sertificate .

# openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem

# openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

Copy the keys to Slave server

# scp ca-cert.pem client-cert.pem client-key.pem   [email protected]:/var/lib/mysql/ssl

Master Side configuration

#  vi /etc/my.cnf

#bind-address = 127.0.0.1
ssl
ssl-ca=/var/opt/mysql/ssl/ca-cert.pem
ssl-cert=/var/opt/mysql/ssl/server-cert.pem
ssl-key=/var/opt/mysql/ssl/server-key.pem
binlog-do-db=mydatabase
server-id = 1
log_bin = /var/lib/mysql/mysql-bin.log

Note that server id should be unique here for master its 1.

Restart mysql and confirm now ssl values are showing properly in ” mysql > show variables like ‘%ssl%’ ;  ”

# mysql –u root –p

GRANT all privileges ON *.* TO [email protected]'192.168.1.2' IDENTIFIED BY 'password' REQUIRE SSL;

Slave Side Configuration

# vi /etc/my.cnf

bind-address = 0.0.0.0
server-id=2
master-host=192.168.1.1
master-connect-retry=60
replicate-do-db=mydatabase
replicate_ignore_db=dataold
replicate_ignore_db=data_duplicate
slave-skip-errors=all
relay-log=mysql-relay-bin.log

check master status on the master node

mysql > show master status ;

| File | Position | Binlog_do_db | Binlog_ignore_db | +

| mysql-bin.002 | 80600 | mydatabase | | +

Update the log location and Position on Slave

Msql > slave stop;

Mysql > CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE=' mysql-bin.002', MASTER_LOG_POS=80600, MASTER_SSL=1, MASTER_SSL_CA = '/var/opt/mysql/ssl/ca-cert.pem', MASTER_SSL_CERT = '/var/opt/mysql/ssl/client-cert.pem', MASTER_SSL_KEY = '/var/opt/mysql/ssl/client-key.pem';

Mysql > slave start ;

Mysql > show slave status \G ;

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.1
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 12345100
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 11381900
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
Replicate_Do_DB: mydatabase
Replicate_Ignore_DB:

The above lines which are marked in green shows that replication is working fine from master to slave.

Follow

mysqldump: Error : Table is marked as crashed and should be repaired when dumping table

Usually we will get this error while dumping database .

Solution :-

1) Repair the crashed table

# mysql -u user -p pass

>  use database ;

> REPAIR TABLE `tablename`;

2) Repair the complete database

#  check for errors or which are tables are corrupted or not closed properly

# cd /var/lib/mysql/DATABASE

# myisamchk -s *.MYI

will give the status

# myisamchk -r  *.MYI

Follow

If we are dumping big db’s we can see this error usually , because the default value of max_allowed_packet is 25M.

Solution :-

Two ways you can fix this

1) Add the following in your /etc/my.cnf and restart mysql

[mysqldump]
max_allowed_packet = 500M

2) Use mysqldump with the option –max_allowed_packet

mysqldump -u root -p --max_allowed_packet=512M  -B  databaseName  -- tables  tablename > dbname.sql