Remote Mysql Server
Remote Mysql Server
Remote Mysql Server
Topics
Overview of Replication & Clustering Integration with cPanel Caveats and Things to Come
Replication
Replication Master/Slave
slave 1 Master slave 3 slave 2
Advantages
Replication - Advantages
Flexible Modest Requirements Simple to setup
Disadvantages
Replication - Disadvantages
Slaves update sequentially Slaves not guaranteed in sync Read from Many; Write to One Optimized for reads
Clustering
Clustering
Peer to Peer
1
Clustering Nodes
Management node handles configuration, logging SQL Node like regular MySQL server Data Node stores data - Can run multiple Nodes on a physical machine
Advantages
Clustering - Advantages
Good performance
Disadvantages
Clustering - Disadvantages
Only NDB tables shared Needs a lot of RAM Must use Carrier Grade version as of 5.1.24 Application must use transactions More Complex
Summary
Replication http://dev.mysql.com/doc/refman/5.1/en/replication.html Clustering http://dev.mysql.com/doc/refman/5.1/en/mysqlcluster.html
Integration
Scenarios
1) Single Remote Server 2) Group of Servers 3) Non Default port
Scenario #1
Single Remote Server
Scenario 1
host1.example.com (25.01.861.291)
db1.example.local (01.01.861.291)
Scenario 1
Scenario 1
Manual 1. Grant remote access to cPanel root account 2. Add remote host info to /root/.my.cnf 3. Configure PHPMyAdmin 4. Configure and migrate Horde 5. Configure and migrate Roundcube 6. Configure and migrate eximstats 7. Configure and migrate cPHulkd 8. Configure and migrate leechprotect 9. Configure and migrate mod_security
Scenario 1
Existing servers: 10. Migrate user databases
Scenario 1 - Manual
1. On remote server - Grant remote access to cPanel root account
mysql> GRANT ALL PRIVILEGES ON *.* TO root@`192.168.10.52` IDENTIFIED BY 'password' WITH GRANT OPTION; mysql> GRANT ALL PRIVILEGES ON *.* TO root@`host1.example.com` IDENTIFIED BY 'password' WITH GRANT OPTION;
Resolving /etc/hosts
Scenario 1 - Manual
2. On cPanel server - add remote host info to local / root/.my.cnf
[client] user=root pass=mypassword host=db1.example.local [host=192.168.10.10]
Scenario 1 - Manual
3. Configure PHPMyAdmin
/usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php ... $i++; $cfg['Servers'][$i]['host'] $cfg['Servers'][$i]['port'] $cfg['Servers'][$i]['socket'] $cfg['Servers'][$i]['connect_type'] $cfg['Servers'][$i]['extension'] $cfg['Servers'][$i]['compress'] ... = 'db1.example.local'; = ''; = ''; = 'tcp'; = 'mysql'; = FALSE;
Scenario 1 - Manual
4. Configure and Migrate Horde A) Configuration
/usr/local/cpanel/base/horde/config/conf.php:
$conf['sql']['hostspec'] = 'db1.example.local';
B) Create / Migrate Horde Database i. Create (a): horde/scripts/sql/create.mysql.sql i. Create (b): /usr/local/cpanel/bin/update-horde ii. Move data
Scenario 1 - Manual
5. Configure and Migrate Roundcube A. Configuration B. Create/Migrate database i. /usr/local/cpanel/bin/update-roundcube i. Copy data
Scenario 1 - Manual
6. Configure and Migrate eximstats A) Configuration B) Create/Migrate database i. cpanel/etc/eximstats.sql ii. Dump/Restore or Migrate physical files
Scenario 1 - Manual
7. Configure and Migrate cPHulkd A) Configuration B) Create/Migrate database i. cpanel/bin/hulkdsetup ii. Dump/Restore or Migrate physical files
Scenario 1 - Manual
8. Configure and Migrate leechprotect A) Configuration
/usr/local/cpanel/bin/updateleechprotect
Scenario 1 - Manual
9. Configure and Migrate mod_security A) Configuration B) Create/Migrate database i. Copy Data
Scenario 1
10. Copy User Data
Scenario #2
Group of Remote Servers
Scenario 2
Use Proxy Presents groups as a single server HA Proxy http://haproxy.1wt.eu/ Load balancing High Availability
Scenario 2
proxy.example.local host1.example.com
db1.example.local
db2.example.local
db3.example.local
Scenario 2
/etc/haproxy.conf
listen mysql *:3306 balance roundrobin server db1 192.168.128.90:3306 server db2 192.168.128.91:3306 server db3 192.168.128.92:3306
Scenario 2
WHM Remote MySQL Server setup won't work Perform 9 manual steps from scenario 1 Variance - /root/.my.cnf
host=proxy.example.local
Scenario #3
Non Default Port
Scenario 3
host1.example.com
m4-1.example.local m4-2.example.local
m5-2.example.local
Scenario 3
listen mysql *:3306 balance roundrobin server m5-1 172.16.0.10:3306 server m5-2 172.16.0.11:3306
m5-1.example.local
m5-2.example.local m4-1.example.local
listen mysql *:3316 balance roundrobin server m4-1 172.16.0.100:3306 server m4-2 172.16.0.101:3306
m4-2.example.local
Scenario 3
Use 9 step manual method from Scenario 1 9 Variations 1. Customize cPanel - 2 items a. Add port entry to my.cnf /root/.my.cnf /home/user/.my.cnf
[client] port=3316
Scenario 3
b. Add support for ports to /usr/local/cpanel/Cpanel/Mysql.pm
sub new { my $self = { 'hasmysqlso' => 0, }; eval { my $dbpass = Cpanel::MysqlUtils::getmydbpass('root') || ''; my $dbserver = Cpanel::MysqlUtils::getmydbhost('root') || 'localhost'; my $dbport = 3316; $self->{'dbh'} = DBI->connect( "DBI:mysql:mysql:$dbserver:$dbport", 'root', $dbpass ); $self->{'hasmysqlso'} = 1; }; if ($@) { print "<br /><b>Error while connecting to MySQL. Failover enacted.</ b><br />\n"; Carp::cluck $@; print "<br />"; } $self->{'pwstring'} = $Cpanel::CONF{'usemysqloldpass'} ? 'old_password' : 'password'; $self->{'host'} = $dbserver; return bless $self, __PACKAGE__; }
Scenario 3
3. Add support for ports to Horde
/usr/local/cpanelo/base/horde/config/conf.php
$conf['sql']['port'] = 3316
Scenario 3
4. Add support for ports to PHPMyAdmin
/usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php
$cfg['Servers'][$i]['port'] = '3316';
Scenario 3
5. Add Port support to Roundcube
/usr/local/cpanel/base/3rdparty/roundcube/config/db.inc.php $rcmail_config['db_dsnw'] = 'mysql:// roundcube:My3ipj5jzYrTuUgo@localhost:3316/roundcube';
Scenario 3
6. Customize Eximstats
/usr/local/cpanel/Cpanel/TailWatch/Eximstats.pm
"DBI:mysql:$self->{'internal_store'}{'dsn'}{'database'}:$self>{'internal_store'}{'dsn'}{'dbhost'}:3316",
Scenario 3
7. Add DB Port to LeechProtect /usr/local/cpanel/bin/leechprotect
sub dbconnect { $dbh = DBI->connect( "DBI:mysql:database=$database:host=$dbhost: 3316", "$dbuser", "$dbpassword", {'RaiseError' => 1 } );
Scenario 3
8. Add support for ports to cPHulkd /usr/local/cpanel/Cpanel/Hulkd.pm:
eval { $dbh = DBI->connect( "DBI:mysql:$db:$host:3316", $user, $pass ); };
Scenario 3
9. Add support for ports to mod_security
/usr/local/cpanel/whostmgr/docroot/cgi/addon_modsec.cgi
my $dbhost ... my $dsn = 'localhost'; = "DBI:mysql:${dbname}:${dbhost}:3316";
Inform users
Existing Servers
Known Caveats
End user applications Single global server Single global port Measuring bandwidth Name collision Changes overwritten
Things to Come
Improved support for clustering Decoupling of username to database name cPanel 12
Questions?