Move MYSQL DB to another Server

Discussion in 'Installation/Configuration' started by reason8, Apr 2, 2016.

  1. reason8

    reason8 Member

    Hello and good afternoon.
    Just wondering if you had any update for any of my responses.
     
  2. reason8

    reason8 Member

    Hello Florian,
    Any word, update, recommendation or follow up on this?
     
  3. reason8

    reason8 Member

    Any response to this at all?

    You lead me in some directory but I am still not sure if my setup is correct.

    ISPCOnfig is working but I do not see the databases on the DB-Server under /var/lib/mysql.

    They are on the primary server so I am not sure if replication is happening or not.

    I just tried to create a new database, but could not login with the username and password.

    I had to disable the DB-Server, enable DB-Server from the primary webserver and I was able to login to phpmyadmin.

    So I do not think something is configured properly.
     
  4. florian030

    florian030 ISPConfig Developer ISPConfig Developer

    This means, that you did not create the database on your db-server. You are creating the database on your webserver.
     
  5. reason8

    reason8 Member

    How do I create the database on the DB-Server?

    Earlier in this thread, I asked for the procedure for doing this.

    I asked you if I should import it and you said no.

    You said to edit the my.cnf and exclude dbispconfig from replication.

    This is what I have for the two my.cnf

    Master Webserver:

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

    binlog_ignore_db = dbispconfig
    replicate-ignore-db=dbispconfig

    # Remove leading # if you want to store your database elsewhere
    # datadir = /var/lib/mysql

    # required unique id between 1 and 2^32 - 1
    # defaults to 1 if master-host is not set
    # but will not function as a master if omitted
    server-id = 1

    New DB-Server:

    server-id = 2

    binlog_ignore_db = dbispconfig
    replicate-ignore-db=dbispconfig

    What is the process for getting the databases on the DB-Server?

    Thanks!
     
  6. reason8

    reason8 Member

    What *Exa
    What *exactly* does this mean?
    Migrate the databases and users? (How?)
    Change everything for website with mysql? (Where do I do this and how?)
     
  7. reason8

    reason8 Member

    HOW do I export JUST the databases for the users to import?
    Details would be nice as there is no export users option in ISPConfig.
     
  8. reason8

    reason8 Member

    Hello and good evening.
    Any update on this?
    After months of thinking the DB server was working, it was not.
    I tried to create a new wordpress site and the database did not get created.
    I had to disabled the DB, renable it on the webserver and then I was able to create the wordpress database.
    I am definitely missing something.
    Am I creating a master slave replication or using the DB server as the only DB server? With NO replication occurring.
    I am looking for a dedicated MYSQL server for ISPconfig... to offload the mysql components from running on the webserver.
    Update:
    After creating the new database with DB server enabled on the webserver and DB server disabled on the DB Server, I can then switch DB server OFF on the webserver and ON on the DB server and the server runs just fine.
    However, new databases can not be created with the DB server running at the dedicated DB server. But the webserver seems to run fine acceessing all databases.

    So there seems to be an issue creating new databases on the dedicated DB server.
    I have run the mysql commands to give the DB server root privileges so this should not be an issue unless there is something else I am supposed to do.

    Any recommendations would be appreciated.
    Thanks!
     
    Last edited: Nov 2, 2016
  9. Jesse Norell

    Jesse Norell Well-Known Member

    Databases on a slave db server are created by the ispconfig cronjob; try downloading the ispconfig installation tarfile for the version you're using, re-run update.php on the DB server and answer yes to have it reconfigure permissions in the master server, and also yes to configure the cronjob again. Make sure cron / the ispconfig cronjob are running. If databases still aren't created, debug the cronjob/server.sh to see why it's failing.
     
  10. reason8

    reason8 Member

    Hey Jesse,

    Thanks for the reply.
    When I run the update.php, it asks for the master db server. By default, its the web server. Should I leave it as this or replace it with the db server I am running the update on?
     
  11. Jesse Norell

    Jesse Norell Well-Known Member

    From browsing this thread, you are using a multi-server install, correct? You have a master ispconfig server, and an ispconfig slave db server? In that configuration, when you run the ispconfig update on the slave (db server), put the hostname or ip address of your master server as "master db server" (it should default to that). You will also need to provide the mysql root password of the master server. The slave ispconfig server connects to mysql on the master ispconfig server, both for normal operations like creating databases, as well as during ispconfig updates (as root mysql user) to change permissions.
     
  12. reason8

    reason8 Member

    Yes. I originally had the master server running DB, Web, etc.
    Then I created a multiserver and setup a separate DB server.
    I did as you instructed and selected the master server. I entered all credentials for the mysql server and reconfigered the permissions.
    I have created a test database and it is still not showing up in phpmyadmin.
     
  13. Jesse Norell

    Jesse Norell Well-Known Member

  14. reason8

    reason8 Member

    I ran the script for debugging. Everything seems to be running as it should.
    Is there a log file I can check that would show what ispconfig is or is not doing as far as mysql goes?
    The DB Server has no such log.
     
  15. Jesse Norell

    Jesse Norell Well-Known Member

    Perhaps you missed the first step, 'Enable the debug Loglevel in ISPConfig' ?
     
  16. reason8

    reason8 Member

    Ok. I have enabled debugging on both servers.
    I also just ran the debug script.
    Where do I find the logs with the debug information?
     
  17. Jesse Norell

    Jesse Norell Well-Known Member

  18. reason8

    reason8 Member

    The only thing I am seeing is:

    Remove Lock: /usr/local/ispconfig/server/temp/.ispconfig_lock
    Set Lock: /usr/local/ispconfig/server/temp/.ispconfig_lock
    It is repeating itself in my logs.
     
  19. Jesse Norell

    Jesse Norell Well-Known Member

    If there are pending changes (eg. database users/databases to be created) and running server.sh manually on your database server with debug level set doesn't say more than that, maybe you don't have the plugin enabled? See if the /usr/local/ispconfig/server/plugins-enabled/mysql_clientdb_plugin.inc.php symlink exists.
     
  20. reason8

    reason8 Member

    Yes. It exists on the DB Server.
    I did join the DB Server to the Main Webserver awhile back. The DB server shows up in the ISPConfig dashboard.
     

Share This Page