My current mysqldb is slow due to more data. So we are planning to take a copy of production db into another instance in the same server and redirect all the select queries to that instance without affecting the production one. Is it possible we can syncronize all the changes from the prodcution to the second instance periodically in the same server ?
Mysql – Is it possible to take same copy of thesql database in another instance(periodically) in the same server
MySQL
Related Solutions
I can see mysqldump being a little pain in the neck because of transaporting data out and then in.
Let's say you are copying data from proddb to stagedb. Here is a query to create the script for lateral copying of every table:
select
concat
(
'drop table if exists ',db2,'.',tbl,'; ',
'create table ',db2,'.',tbl,' like ',db1,'.',tbl,'; ',
'insert into ',db2,'.',tbl,' select * from ',db1,'.',tbl,';'
)
from (select table_schema db1,table_name tbl
from information_schema.tables where table_schema='stack_dev') tbls,
(select 'stack_test' db2) dbname;
Just generate the script as a SQL text file and import the text file to the target server:
PROD_IP-10.1.2.20
STAG_IP=10.1.2.40
mysql -h${PROD_IP} -uusername -p --skip-column-names -A -e"select concat('drop table if exists ',db2,'.',tbl,'; ','create table ',db2,'.',tbl,' like ',db1,'.',tbl,'; ','insert into ',db2,'.',tbl,' select * from ',db1,'.',tbl,';') from (select table_schema db1,table_name tbl from information_schema.tables where table_schema='stack_dev') tbls,(select 'stack_test' db2) dbname;" > /root/CopyFromProdToStage.sql
mysql -h${STAG_IP} -uusername -p -A < /root/CopyFromProdToStage.sql
This should work just fine for MyISAM. It should also work for InnoDB if there are no constraints. If there are constraints, you may have to disable them for the import session:
PROD_IP-10.1.2.20
STAG_IP=10.1.2.40
echo "SET FOREIGN_KEY_CHECKS=0;" > /root/CopyFromProdToStage.sql
mysql -h${PROD_IP} -uusername -p --skip-column-names -A -e"select concat('drop table if exists ',db2,'.',tbl,'; ','create table ',db2,'.',tbl,' like ',db1,'.',tbl,'; ','insert into ',db2,'.',tbl,' select * from ',db1,'.',tbl,';') from (select table_schema db1,table_name tbl from information_schema.tables where table_schema='stack_dev') tbls,(select 'stack_test' db2) dbname;" >> /root/CopyFromProdToStage.sql
mysql -h${STAG_IP} -uusername -p -A < /root/CopyFromProdToStage.sql
UPDATE 2012-03-09 17:14 EST
I have a stored procedure that will accomplish this. Run the following only once:
DROP DATABASE IF EXISTS utility;
CREATE DATABASE utility;
DELIMITER $$
DROP PROCEDURE IF EXISTS `utility`.`CopyDB` $$
CREATE PROCEDURE `utility`.`CopyDB` (sourceDB VARCHAR(64),targetDB VARCHAR(64))
TheStoredProcedure:BEGIN
DECLARE found_count,ndx,ndx_last INT;
DECLARE sqlcmd VARCHAR(1024);
SELECT COUNT(1) INTO found_count
FROM information_schema.tables
WHERE table_schema = sourceDB;
IF found_count = 0 THEN
LEAVE TheStoredProcedure;
END IF;
DROP TABLE IF EXISTS DBTablesToCopy;
CREATE TABLE DBTablesToCopy
(
id INT NOT NULL AUTO_INCREMENT,
src VARCHAR(64),
tgt VARCHAR(64),
tbl VARCHAR(64),
PRIMARY KEY (id)
) ENGINE=MyISAM;
DROP TABLE IF EXISTS SQLScriptToCopyTables;
CREATE TABLE SQLScriptToCopyTables
(
id INT NOT NULL AUTO_INCREMENT,
sqltext VARCHAR(1024),
PRIMARY KEY (id)
) ENGINE=MyISAM;
INSERT INTO DBTablesToCopy (src,tgt,tbl)
SELECT sourceDB,targetDB,table_name
FROM information_schema.tables
WHERE table_schema = sourceDB;
INSERT INTO SQLScriptToCopyTables (sqltext) VALUES
(CONCAT('DROP DATABASE IF EXISTS ',targetDB));
INSERT INTO SQLScriptToCopyTables (sqltext) VALUES
(CONCAT('CREATE DATABASE ',targetDB));
SELECT MAX(id) INTO ndx_last FROM DBTablesToCopy;
SET ndx = 0;
WHILE ndx < ndx_last DO
SET ndx = ndx + 1;
INSERT INTO SQLScriptToCopyTables (sqltext)
SELECT CONCAT('CREATE TABLE ',tgt,'.',tbl,' LIKE ',src,'.',tbl)
FROM DBTablesToCopy WHERE id = ndx;
END WHILE;
SET ndx = 0;
WHILE ndx < ndx_last DO
SET ndx = ndx + 1;
INSERT INTO SQLScriptToCopyTables (sqltext)
SELECT CONCAT('INSERT INTO ',tgt,'.',tbl,' SELECT * FROM ',src,'.',tbl)
FROM DBTablesToCopy WHERE id = ndx;
END WHILE;
SELECT MAX(id) INTO ndx_last FROM SQLScriptToCopyTables;
SET ndx = 0;
WHILE ndx < ndx_last DO
SET ndx = ndx + 1;
SELECT sqltext INTO @stmt FROM SQLScriptToCopyTables WHERE id = ndx;
PREPARE s1 FROM @stmt;
EXECUTE s1;
DEALLOCATE PREPARE s1;
END WHILE;
END $$
DELIMITER ;
then anytime you feel like copying stack_dev
to stack_test
, do this:
call utility.copydb('stack_dev','stack_test');
and that's all.
Give it a Try !!!
Please keep in mind that prior to MySQL 5.1.38, InnoDB was not designed to access multiple CPUs. You have one of two options to get MySQL to take advantage of multiple CPUs.
- OPTION 1 : Install MySQL 5.5
- OPTION 2 : Install MySQL 5.1 InnoDB Plugin
There are new options available since MySQL 5.1.38 (InnoDB Plugin Only) that is now fully available in MySQL 5.5
Rather rewrite what I wrote in past posts about this subject, please reads my past posts discussing those new settings:
- About single threaded versus multithreaded databases performance
- Possible to make MySQL use more than one core?
- Multi cores and MySQL Performance
As for tuning all other aspects of MySQL, please refer to my past post about looking into disk space management and storage engine tuning : MySQL 5.1 InnoDB Configuration / 24GB RAM - bi-xeon high load
Related Question
- MySQL on RDS, data transfer from one instance to another as a production job
- MySQL – Copy On Write Transparent Database
- Mysql – the best way to upgrade a MySQL version in a Linux Server
- Mysql – the best way to upgrade a MySQL version in a Linux Server
- MySQL – How to Migrate Schema Structure from One Server to Another
- MySQL – How to Copy Table from One Server to Another
Best Answer
Yes , what you need is a replication environment !
Where your primary server (Master) will receive all updates and inserts, basiclly all DML's and the secondary server (slave) will receive only select statements ! Here is a link to an article showing you how to do this.
As for the load-balancing , in my case i use F5 to do this for me ! Here is link of the article that shows how this is done ! i have to worn you is quite hard to setup this puppy !
To run multiple instances using MySQL you need to have a couple of things separate from the initial install on MySQL like data directory, init script and config file. Ok i will place the steps here : -all you need is for you complete them:
Edit the init file and make some minor changes to make it this instance specific - like port number ,instance name, take care at this step !!
Install default tables for this new database instance
mysql_install_db --datadir=/var/lib/mysql_new --defaults-file=/etc/my_new.cnf --user=mysql
Start the new instance
Set password for this instance and connect to this new instance
Finally add it to server start-up list
And now setup your replication.