Move MYSQL DB to another Server

Hello and good evening.
I am currently running ISPConfig 3 on OpenSuse 13.1
It is running as it should (in full production).
However, I would like to move the MYSQL database to it's own dedicated server.
Why? Well MYSQL is using ALOT of resources on the Webserver and I would like to offload it onto a dedicated machine (virual server).
I know how to export and import databases but I am not sure as to the intricacies of the data migration.
It someone could point me in the right direction, I would greatly appreciate it!

 

Create a new server for mysql, let the server join your ispconfig setup, migrate the datases and users, change the server_id in the master-database and don`t forget to change everything for websites with mysql (you can`t use localhost anymore),

 

When you say "create a new server..." do you mean create a new ISPConfig Server (using the Perfect Server documentation)?
Or do I just install mysql-community server and mysql (with dependencies)?
How do I let the server join the ispconfig setup? How do I migrate the databases and users? Via a mysql dump and import?

 

I would use the perfect setup. Installing ispconfig in the expert-mode allows you to join an existing setup.
Change the server_id for databases and db-users in the master-database (your current ispconfig-server) to the new server-id and resync the databases. afterwards dump the databases and import them.

 

So what do I enter after selecting EXPERT settings?

FQDN newserver.domain.tld
MYSQL Server hostname [localhost]: (Do I enter the IP address of the new mysql server?)

This is as far as I have gotten.

 

I've been reading about this and it seems most information is for a Master-Slave configuration where the data from the Master (ISPConfig Server 1) is replicated to Server 2.
I don't want MySQL queries being ran at all on the original ISPConfig Server.
I want it running purely off of the new server.
Is this possible?
Can I simply bind the MYSQL address in /etc/my.cnf to the Server 2 IP, drop the tables from server 1, import into server 2 and and have server 1 query and store data on server 2?

 

Ok, I got the new database server (server2) created, connected and joined to the original server (server1)
Now is mysql supposed to still run on the original server (server1?
I am going to disable DB server on server1 and enable on server2 (per the ISPConfig admin page).
Will this stop queries from occurring on the main webserver?
I am trying to minimize the server impact from MYSQL running on the webserver.
Thanks!

 

I just resynced and imported the database into the new server.
Questiion: should I have imported into the dbispconfig database as follows:
mysql -u -p dbispconfig < database.sql

The reason I am asking is because, when I entered "show tables:"
all of my database tables were shown (in addition to the dbispconfig.

Shouldn't it have just shown the default mysql tables plus dbispconfig?
And then when I changed to the dbispconfig using "use dbispconfig" then it should show my tables?
Just wondering if the tables got imported to their correct location.

 

You need mysql on each server (ispconfig needs a small database). Do not import dbispconfig - each server needs it`s own local dbispconfig. Just import the databases for the users (NOT for ispconfig).

 

What exactly do I need to export and import?
I exported the dbispconfig.
What should I have exported? What is the users database?

 

Question: Am I setting up master-slave replication as per the mysql documents located here: http://dev.mysql.com/doc/refman/5.7/en/replication-howto.html

Do I need to configure the my.cnf files on the master or slave machines?
I followed the How To Perfect Server for OpenSuse 13.1 for setting up the Master and for setting up the DB Server.
I also followed the Mutiple Server Setup with Dedicated... located here: https://www.howtoforge.com/installi...tabase-servers-on-debian-5.0-with-ispconfig-3.
I have disabled the DB server from the master and enabled it for the DB Server.
Is there anything else I need to do?
I resync the clientDB databases within ISPConfig but I did not import anything into t he DB server (I didn't know which databases to export and import).
Is there anything I am missing?
Everything seems to be running fine but I am still unsure if it is configured correctly.

 

For a replication? Change it on both servers.

You must change the server_id for the databases in dbispconfig on your master-server.

If you run a mysql-replication the databases should be created on the slave so you don`t need to import anything. Just make sure, that excluede dbispconfig from the replication or use different databasenames for ispconfig on both servers.

 

How do I exclude the dbispconfig from replication? What are the damages if it is not? I have been running with the above configuration for over a week. The load on the master server seems to have gone down significantly but I am not sure if I have done everything you recommend.

 

You can exclude databases / tables for sql-replication in your my.cnf (replicate-ignore-db= or replicate-wild-ignore-table=)

 

So replicate-ignore-db=dbispconfig ?
Does this go in the master my.cnf, the slave or both?
Does it go in the /etc/my.cnf or the /usr/my.cnf or both?
Does it matter if I have been running the server without these exclusions?

 

I have added the below to my master-server db (/etc/my.cnf):
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = dbispconfig

Does it need to be included or removed?

 

So I have added the following to my /etc/my.cnf (master)
binlog_ignore_db = mysql
binlog_ignore_db = dbispconfig
binlog_ignore_db = information_schema

Would these be considered adequate values?
Also, should this be added to the master AND the slave? or just the master?

Remember, I am wanting to offload the mysql workload from the master and put it on the slave. Is this a correct configuration?

 

How do I know if the replication is working?
How do I check to see if the new DB server is actually being replicated TO and that ISPConfig is using it for the database queries instead of the original Master server?

 

How do I know if the replication is working?
How do I check to see if the new DB server is actually being replicated TO and that ISPConfig is using it for the database queries instead of the original Master server?

 

I'm adding a new database and I noticed ISPConfig has the new DB server listed for its creation.

However, the other DB Servers are blank because they were part of the original install.

Do these need to be changed?