How to setup MySQL master -> slave replication

Posted febrero 23rd, 2012 in Blog, DataBases by Gastón Acosta Ramas

Hi, again.
here i will show you how to setup a master/slave, statement based, MySQL replication.

first of all we’re going to configure the master server.
this is what i added to my my.cnf file.

let’s create a user for the replication:

now we need a snapshot, or a dump from the master to put it into the server.
here are two ways. you need to lock the database to get synced the binlog pos and the data in the dump.
If you’re using MyISAM you can do this way:

if you’re using InnoDB you can do smth like this:

now go to the slave server.
add this lines into my.cnf

now stop the slave.

import the dump.

get the master info from the dump.

with the output of this command you should run a mysql query.
this query is to tell mysql slave in which position and file it begin to replicate.
if you have ran “SHOW MASTER STATUS”, you will use this info to run the change master query.

All done. start the slave and verify it’s running

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.10
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000020
Read_Master_Log_Pos: 1375140
Relay_Log_File: mysqld-relay-bin.000005
Relay_Log_Pos: 1375234
Relay_Master_Log_File: mysql-bin.000020
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: wordpress_rep
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: 1375140
Relay_Log_Space: 1375234
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)

the three most important parameters for this output are :
Seconds_Behind_Master: 0
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

things to be carefull…

  1. in this example we’ve setted up a statement based replication, so all actions in master must be replicated in the slave, i mean triggers and functions
  2. mysql has a bug, documented here
    http://bugs.mysql.com/bug.php?id=10830, because of this bug, if you don’t execute “use bbdd” before make a query, this query is not logged into binlog, so it’s not replicated to the slave. BE CAREFULL.

Good Replicating!.

How to know database size if you’re using InnoDB

Posted febrero 15th, 2012 in Blog, DataBases by Gastón Acosta Ramas

if you’re using MyISAM engine and want to know the size of your database,  you can get this info by running a simple du for the /var/lib/mysql directory

here you can easily read the size of each database.

BUT, if you’re using InnoDB engine, specially if you have innodb_file_per_table setted on, the “du method” doesn’t work. So you can run this.

if you want specific databse size, or if you want all tables sizes from specific dbase, you can run this.

obviously you need to replace ‘DB’ with your db name, e.g ‘wordpress_1′
if you want all databases sizes, you should run: