Browsed by
Category: Mysql

Mysql DB size

Mysql DB size

With the following query you will get summary of all DB size

SELECT table_schema AS "Database",
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES
GROUP BY table_schema;

You can also explode single DB and get size of each table with the following query (change db_name with the name of database you want to analize)

SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = "db_name"
ORDER BY (data_length + index_length) DESC;

Otherwise you can get size of table on all database sort by size with the following queyr

SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;
Create a MySQL Slave using Replication with No Downtime

Create a MySQL Slave using Replication with No Downtime

2013060316014742_1

I have a customer who has over 100GB of MySQL data and taking their site down for even a few minutes is not feasible. I really wanted to get a slave set up in case the main server ever dies. Even though the server is backed up, it would take 2-3 hours (or longer) to restore the MySQL server which is not very acceptable.

The solution is to use replication. The traditional problem with this approach is locking the tables for so long while the mysqldump happens… for a database this size, close to 4-5 hours.

Idera’s Free Tool called Linux Hot Copy (hcp) was the answer I was looking for. By using hcp, you can lock the tables, make a near instant “snapshot”, record the master position, and unlock the tables. At your leisure, just copy the snapshot of the mysql data to your slave device, and start up your replication! This makes setting up new slaves a snap with minimal impact on your business.

First off, I will assume you have a production MySQL server in use and running. In my scenario, I am using CentOS 5.6 64Bit and MySQL 5.5. This tutorial will probably will work for older versions as well. I also will assume you know how to edit and copy files at the linux command line. If you don’t, you probably should get help from an experienced system administrator.

If you have not done so already, set up another mysql server for your slave. It should be a decent server, equal to your current live production server so you can switch to it in the event of failure.

I will also assume:

master server = 192.168.1.100
slave server = 192.168.2.200

You’ll need to substitute your IP Addressess in place of mine.

On Master Server (192.168.1.100):

1. Install Linux Hot Copy. Linux Hot Copy. If you need help with installation, here’s some documentation

2. Setup your Server ID and enable bin-logs. Note that bin logs record every change to your database, so make sure you have ample space to continue!)

Edit your /etc/my.cnf file and put these lines at the top, just under the [mysqld] line.

# enable mysql bin logs and server-id for mysql replication
       log-bin=mysql-bin
       server-id=1

Restart MySQL so bin logs are started. e.g. /etc/init.d/mysql restart you can verify it’s working by issuing the show master statusG command.

3. Create a user that has replication privs on the Master Server.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.2.200' IDENTIFIED BY 'password';

4. The next few steps will need to be done quickly so that you minimize your mysql server’s downtime. Make sure you know up-front the device (e.g. /dev/sda2) where your MySQL installation is located (typically /var/lib/mysql on CentOS):

Lock your Master MySQL Tables and show the status location of the bin log….

mysql> FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;

Make sure you record and copy the information down, e.g. the filename and log position

From the command line, enter the following command, replacing /dev/sda2 with your raw device:

hcp /mnt/snap /dev/sda2

Back to MySQL, unlock your tables:

mysql> unlock tables;

Now you have a perfect copy of your “frozen” data at the following location: (may vary)..

FROZEN DATA LOCATION:
/mnt/snap

On Slave Server: 192.168.2.200

On the slave server, make sure MySQL is stopped and move the old mysql folder: (make sure this is the SLAVE SERVER 192.168.2.200 and NOT the live server!):

/etc/init.d/mysql stop
mv /var/lib/mysql /var/lib/mysql.old

Back on the Master Server: 192.168.1.100

1. Copy the “frozen” mysql data:

rsync -avz /mnt/snap  [email protected]:/var/lib/mysql

2. Copy my.cnf to slave:

scp /etc/my.cnf [email protected]_or_host:/etc/my.cnf

3. Once the Copy is Complete you can delete your “hot copy”

hcp -r /dev/hcp1

Now, go to your Slave Server: 192.168.2.200

1. edit /etc/my.cnf and change server-id to 2 and comment out or delete the log-bin line you added from the master..

2. start up mysql, and then enter commands to connect to master.. replacing the log file and position number with the ones you recorded earlier:

mysql> CHANGE MASTER TO 
      MASTER_HOST='192.168.1.100', 
      MASTER_USER='repl', 
      MASTER_PASSWORD='password', 
      MASTER_LOG_FILE='mysql-bin.000001',
      MASTER_LOG_POS=12345678;
mysql> START SLAVE;

 mysql> SHOW SLAVE STATUS/G;

MySQL will show how far it’s behind, it might take a few minutes to catch up depending on the number of changes that happened to your database during the copy.

I hope you enjoyed this tutorial on MySQL Replication with no downtime. Now it’s easy!

Dumping and importing from/to MySQL in an UTF-8 safe way

Dumping and importing from/to MySQL in an UTF-8 safe way

In a nutshell: to avoid your shell character set from messing with imports, use -r to export and SOURCE when importing.
Dumping safely

Do not do this, since it might screw up encoding:

mysqldump -uroot -p database > utf8.dump # this is bad

Better do:

mysqldump -uroot -p database -r utf8.dump

Note that when your MySQL server is not set to UTF-8 you need to do mysqldump –default-character-set=latin (!) to get a correctly encoded dump.

If you only want to dump the structure without data, use

mysqldump -uroot -p –no-data database -r utf8.dump

Importing a dump safely

Do not do this, since it might screw up encoding:

mysql -u username -p database SOURCE utf8.dump