Ignore Mysql Tables using regex when using Mysqldump command

Hi guys,

We can ignore database tables while taking mysqldump using the below command

mysqldump -u username -p database –ignore-table=database.table1 –ignore-table=database.table2 > database.sql

However if we want to ignore more tables we need to add the tables manually and its a real pain in the A$$

Here I am showing how to take the mysqldump using regex to ignore mysql tables.

For example:- If I want to ignore the below tables starting from

1) alice_token,

2) import_

3)sales_order_item_shipment_tracking

4)sales_order_item_status_history

5)stock_import

6)ums

root@vishnu-machine# cd /var/lib/mysql/<database>

root@vishnu-machine:/var/lib/mysql/<database># echo ‘[mysqldump]’ > mydump.cnf

root@vishnu-machine:/var/lib/mysql/<database>#  mysql -NBe “select concat(‘ignore-table=’, table_schema, ‘.’, table_name) from information_schema.tables where table_name REGEXP ‘^(alice_token|import_|sales_order_item_shipment_tracking|sales_order_item_status_history|stock_import|ums)’;” > mydump.cnf

And verify the file “mydump.cnf”

root@vishnu-machine:/var/lib/mysql/<database># cat mydump.cnf

ignore-table=<database>.alice_token

ignore-table=<database>.import_token

ignore-table=<database>.import_sales

ignore-table=<database>.sales_order_item_shipment_tracking

ignore-table=<database>.sales_order_item_status_history

ignore-table=<database>.stock_import

ignore-table=<database>.ums

You can see the above entries in the file “mydump.cnf”

root@vishnu-machine:/var/lib/mysql/<database># mysqldump –defaults-file=mydump.cnf -u root -p <database> > vishnu.sql

And here it is new dump without tables as mentioned above.

 

How to Set Up Master Slave Replication in MySQL

About MySQL replication


MySQL replication is a process that allows you to easily maintain multiple copies of a MySQL data by having them copied automatically from a master to a slave database. This can helpful for many reasons including facilating a backup for the data,a way to analyze it without using the main database, or simply as a means to scale out.

This tutorial will cover a very simple example of mysql replication—one master will send information to a single slave. For the process to work you will need two IP addresses: one of the master server and and one of the slave.

This tutorial will use the following IP addresses:

12.34.56.789- Master Database

12.23.34.456- Slave Database

Setup


This article assumes that you have user with sudo privileges and have MySQL installed. If you do not have mysql, you can install it with this command:

sudo apt-get install mysql-server mysql-client

 

Step One—Configure the Master Database


Open up the mysql configuration file on the master server.

sudo nano /etc/mysql/my.cnf

Once inside that file, we need to make a few changes.

The first step is to find the section that looks like this, binding the server to the local host:

bind-address            = 127.0.0.1

Replace the standard IP address with the IP address of server.

bind-address            = 12.34.56.789

The next configuration change refers to the server-id, located in the [mysqld] section. You can choose any number for this spot (it may just be easier to start with 1), but the number must be unique and cannot match any other server-id in your replication group. I’m going to go ahead and call this one 1. Make sure this line is uncommented.

server-id               = 1

Move on to the log_bin line. This is where the real details of the replication are kept. The slave is going to copy all of the changes that are registered in the log. For this step we simply need to uncomment the line that refers to log_bin:

log_bin                 = /var/log/mysql/mysql-bin.log

Finally, we need to designate the database that will be replicated on the slave server. You can include more than one database by repeating this line for all of the databases you will need.

binlog_do_db            = newdatabase

After you make all of the changes, go ahead and save and exit out of the configuration file.

Refresh MySQL.

sudo service mysql restart

The next steps will take place in the MySQL shell, itself.

Open up the MySQL shell.

mysql -u root -p

We need to grant privileges to the slave. You can use this line to name your slave and set up their password. The command should be in this format:

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';

Follow up with:

FLUSH PRIVILEGES;

The next part is a bit finicky. To accomplish the task you will need to open a new window or tab in addition to the one that you are already using a few steps down the line.

In your current tab switch to “newdatabase”.

USE newdatabase;

Following that, lock the database to prevent any new changes:

FLUSH TABLES WITH READ LOCK;

Then type in:

SHOW MASTER STATUS;

You will see a table that should look something like this:

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 | newdatabase  |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

This is the position from which the slave database will start replicating. Record these numbers, they will come in useful later.

If you make any new changes in the same window, the database will automatically unlock. For this reason, you should open the new tab or window and continue with the next steps there.

Proceeding the with the database still locked, export your database using mysqldump in the new window (make sure you are typing this command in the bash shell, not in MySQL).

mysqldump -u root -p --opt newdatabase > newdatabase.sql

Now, returning to your your original window, unlock the databases (making them writeable again). Finish up by exiting the shell.

UNLOCK TABLES;
QUIT;

Now you are all done with the configuration of the the master database.

Step Two—Configure the Slave Database


Once you have configured the master database. You can put it aside for a while, and we will now begin to configure the slave database.

Log into your slave server, open up the MySQL shell and create the new database that you will be replicating from the master (then exit):

CREATE DATABASE newdatabase;
EXIT;

Import the database that you previously exported from the master database.

mysql -u root -p newdatabase < /path/to/newdatabase.sql

Now we need to configure the slave configuration in the same way as we did the master:

sudo nano /etc/mysql/my.cnf

We have to make sure that we have a few things set up in this configuration. The first is the server-id. This number, as mentioned before needs to be unique. Since it is set on the default (still 1), be sure to change it’s something different.

server-id               = 2

Following that, make sure that your have the following three criteria appropriately filled out:

relay-log               = /var/log/mysql/mysql-relay-bin.log
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = newdatabase

You will need to add in the relay-log line: it is not there by default.

Once you have made all of the necessary changes, save and exit out of the slave configuration file.

Restart MySQL once again:

sudo service mysql restart

The next step is to enable the replication from within the MySQL shell.

Open up the the MySQL shell once again and type in the following details, replacing the values to match your information:

CHANGE MASTER TO MASTER_HOST='12.34.56.789',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  107;

This command accomplishes several things at the same time:

    1. It designates the current server as the slave of our master server.

 

    1. It provides the server the correct login credentials

 

  1. Last of all, it lets the slave server know where to start replicating from; the master log file and log position come from the numbers we wrote down previously.

With that—you have configured a master and slave server. Activate the slave server:

START SLAVE;

You be able to see the details of the slave replication by typing in this command. The \G rearranges the text to make it more readable.

SHOW SLAVE STATUS\G

If there is an issue in connecting, you can try starting slave with a command to skip over it:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;

All done.

Quick MySQL Commands

To Login into the MySQL
#mysql -u username -ppaswword
and if its RDS :
#mysql -h rds.indexpoint/dnsname/ip -u username -ppassword

To List Databases
mysql>show databases;

To View tables
mysql>use databasename;
mysql>show tables;

To View Contents Inside the Table
mysql>select * from tablename;

To Delete Database
mysql>drop database dbname;

To create Database user (we also need to grant permission to a db to gain access for the new user.)
mysql>create user ‘UNNI’@’ipaddress/%/localhost’ identified by ‘passwd3@1′;

To Grant Permission to a Database for a User and Create the User at the same Time
mysql>grant all on databasename.* to ‘UNNI’@’ipaddress/%/localhost’ identified by ‘password’;

[NOTE– To provide access from all IP Address use ‘%’ instead of ipaddress.]

To List out all Database Users
mysql>SELECT user,host FROM mysql.user;

To Create a Database
mysql>create database UNNI;

To restore a specific Database in Mysql (database UNNI has to be created already)
#mysql -u username -ppassword UNNI < UNNI.sql

[NOTE: Database UNNI had to be created before restore]

To dump/backup a specific Database in Mysql
#mysqldump -u username -ppassword UNNI > UNNI.sql

[NOTE: Database UNNI is backed up into UNNI.sql]

To know the permission of Mysql User
#show grants for ‘unni’@’localhost’;

[NEED TO KNOW]- Create/Delete User :::

It must be noted that CREATE USER command was added in the MySQL version 5.0.2. In earlier versions, users could be created automatically when assigning permissions using the GRANT command or by manually inserting records in the mysql database.

The mysql database contains three tables – user, host and db. These tables contains the database permissions.

The user table contains the usernames and password combination of anyone who has access to any part of the MYSQL database. The password part is the encrypted string, which can be generated using the PASSWORD() function.

As an administrator, you can even directly insert the values into the user table of mysql database and get the desired results.
mysql>INSERT INTO user(Host,User,Password) VALUES('localhost', 'UNNI', PASSWORD('passwd1@3'));

mysql>FLUSH PRIVILEGES;

The FLUSH PRIVILEGES command is required to inform MySQL to reload the privilege data after the change is made.

Deleting Users

To delete users from the MySQL database use the DROP command.
mysql>DROP USER user@host;

The command in turn removes the user record from the mysql.user table.

As the CREATE USER command, even the DROP USER command has been added since MySQL 5.0.2. In previous versions of MySQL you must revoke the user’s privileges first, delete the records from user manually and then issue the FLUSH PRIVILEGES command.

mysql>DELETE FROM user WHERE User= 'technofriends' AND Host= 'localhost';
FLUSH PRIVILEGES;

 

 

General Notes

  • There is no concept in MySQL of “Owner” of database or its objects, as there is in MS Access and MS SQL Server. I surmise this from the lack of “owner” field anywhere in mysql system tables.

How to Reset Mysql ROOT Password

1) Stop the mysql demon

/etc/init.d/mysql stop

2) Start the mysqld demon process using the –skip-grant-tables option with this command

/usr/sbin/mysqld –skip-grant-tables –skip-networking &

3) start the mysql client process using this command

mysql -u root

4) from the mysql prompt execute this command to be able to change any password

FLUSH PRIVILEGES;

5) Then reset/update your password

SET PASSWORD FOR root@’localhost’ = PASSWORD(‘password’);

FLUSH PRIVILEGES;

6) Then stop the mysqld process and relaunch it with the classical way:

/etc/init.d/mysql stop
/etc/init.d/mysql start

Mysql- Database Sharding

“share nonthing” : Key Law on database sharding Architecture.
Small Databases are Fast, Big Databases are Slow !!!
DB Sharding – Breaking a Bigger DB into a Smaller DB.

Key Points on DB Sharding –

  • Partition Data across master
  • Writes and read are distributed
  • Application needs modification
  • Needs choice partitioning strategy for uniform data distribution.

Issues –

  • Joins cannot be performed across shards
  • Application modification can be expensive.
  • Example : Evernote uses database sharding – localized failures , no need for joins. Each shards handles all  data &  traffic for about 100,000 users.

Sharding is another way to resolve MySQL scalability issues. It usually means splitting up the data by some logic derived from the application. This can be done by selecting a key in the data and splitting the data by hashing that key and having some distribution logic. It can also be done by identifying the application needs and setting different tables or different data sets in different databases (splitting the North-America sales data from the EMEA sales data, etc.)

This approach is simple from the database standpoint, but is very complex from the application standpoint since the application needs to be modified to deal with the data being scattered into the different shards. Moreover, combining data from different shards can be very complex and involves development in the application (you can’t just run a simple JOIN.)

Advantages of scaling out and in using sharding:

* Scales beyond the limitations of a single machine
* Scales both read and write operations (but makes some operations impossible to achieve in the database)
* Scales both throughput and capacity

Disadvantages of scaling out and in using sharding:

* Complex and requires application changes
* Scaling is usually offline and requires a re-partitioning event – and may require application changes.

Today, there are some solutions that introduce auto-sharding (Scalebase, Dbshards). This approach makes sharding more similar to shared-nothing partitioning, thus taking the sting out of some sharding complexities. However, it still requires application awareness and could prove to be a limiting factor if you needed to update your app or migrate to a different database solution.