Hey guys,
I've been using ssl connections to the database for a little while now
thanks to some of the help I've gotten on the list. Works great!
Here's the settings I'm using to do that:
## Database settings
$wgDBtype = "mysql";
$wgDBservers = '';
$wgDBserver = "db.example.com";
$wgDBssl = true;
$wgDBname = "jfwiki";
$wgDBuser = "admin_ssl";
$wgDBpassword = "secret";
$wgDBprefix = "";
$wgDBTableOptions = "ENGINE=InnoDB, DEFAULT CHARSET=utf8";
$wgDBmysql5 = false;
$wgShowDBErrorBacktrace = true;
Well I actually have a 4 node database cluster. With 2 masters slaving off
each other, and 2 slaves running in read only mode.
The
db.example.com that you see above is actually a VIP that that controls
the two masters via HA/Proxy.
What I'd like to do is accomplish the same thing, SSL db connections, but
splitting off the reads to the two slaves and send only writes to the
masters.
So I found this mediawiki database manual:
https://www.mediawiki.org/wiki/Manual:$wgDBservers
And I tried adapting the example to get what I'm after in terms of sending
only writes to the master VIP and read from the two slaves.
What I find is if I use a non-ssl user this does actually work well:
$wgDBservers = array(
array(
'host' => "db.example.com",
'dbname' => "jfwiki",
'user' => "admin",
'password' => "secret",
'type' => "mysql",
'flags' => DBO_DEFAULT,
'load' => 0,
),
array(
'host' => "db3.example.com",
'dbname' => "jfwiki",
'user' => "admin",
'password' => "secret",
'type' => "mysql",
'flags' => DBO_DEFAULT,
'load' => 1,
),
array(
'host' => "db4.example.com",
'dbname' => "jfwiki",
'user' => "admin",
'password' => "secret",
'type' => "mysql",
'flags' => DBO_DEFAULT,
'load' => 1,
),
);
No problems there. The wiki comes right up!! However if I try the same
thing, but using an SSL user with the SSL flag in the options section,
that's where it all falls apart.
If I use this setup:
## Database settings
$wgDBtype = "mysql";
$wgDBservers = '';
$wgDBservers = array(
array(
'host' => "db.example.com",
'dbname' => "jfwiki",
'user' => "admin_ssl",
'password' => "secret",
'type' => "mysql",
'flags' => DBO_SSL,
'load' => 0,
),
array(
'host' => "db3.example.com",
'dbname' => "jfwiki",
'user' => "admin_ssl",
'password' => "secret",
'type' => "mysql",
'flags' => DBO_SSL,
'load' => 1,
),
array(
'host' => "db4.example.com",
'dbname' => "jfwiki",
'user' => "admin_ssl",
'password' => "secret",
'type' => "mysql",
'flags' => DBO_SSL,
'load' => 1,
),
);
#$wgDBssl = true;
$wgDBprefix = "";
$wgDBTableOptions = "ENGINE=InnoDB, DEFAULT CHARSET=utf8";
$wgDBmysql5 = false;
$wgShowDBErrorBacktrace = true;
With that set in my LocalSettings.php, I get the following error in the
browser:
Sorry! This site is experiencing technical difficulties.
Try waiting a few minutes and reloading.
(Cannot access the database)
Backtrace:
#0 /var/www/jf/mediawiki-1.25.2/includes/db/LoadBalancer.php(807):
DatabaseBase->reportConnectionError('No working slav...')
#1 /var/www/jf/mediawiki-1.25.2/includes/db/LoadBalancer.php(501):
LoadBalancer->reportConnectionError()
#2 /var/www/jf/mediawiki-1.25.2/includes/GlobalFunctions.php(3594):
LoadBalancer->getConnection(-1, Array, false)
#3 /var/www/jf/mediawiki-1.25.2/includes/page/WikiPage.php(369): wfGetDB(-1)
#4 /var/www/jf/mediawiki-1.25.2/includes/page/WikiPage.php(449):
WikiPage->loadPageData()
#5 /var/www/jf/mediawiki-1.25.2/includes/page/WikiPage.php(491):
WikiPage->exists()
#6 /var/www/jf/mediawiki-1.25.2/includes/page/WikiPage.php(215):
WikiPage->getContentModel()
#7 /var/www/jf/mediawiki-1.25.2/includes/page/WikiPage.php(201):
WikiPage->getContentHandler()
#8 /var/www/jf/mediawiki-1.25.2/includes/actions/Action.php(96):
WikiPage->getActionOverrides()
#9 /var/www/jf/mediawiki-1.25.2/includes/actions/Action.php(151):
Action::factory('view', Object(WikiPage), Object(RequestContext))
#10 /var/www/jf/mediawiki-1.25.2/includes/MediaWiki.php(139):
Action::getActionName(Object(RequestContext))
#11 /var/www/jf/mediawiki-1.25.2/includes/MediaWiki.php(481):
MediaWiki->getAction()
#12 /var/www/jf/mediawiki-1.25.2/includes/MediaWiki.php(414):
MediaWiki->main()
#13 /var/www/jf/mediawiki-1.25.2/index.php(41): MediaWiki->run()
#14 {main}
The error seems to be complaining that it can't read from the slaves.
However I've verified that I can connect as the SSL user to both read only
slaves from the command line on each of the 3 web servers I"m using to run
the wiki:
1st web server
[root@ops1:~] #mysql -uadmin_ssl -p -h
db3.example.com -e "show databases"
Enter password:
+--------------------+
| Database |
+--------------------+
| bacula |
| information_schema |
| jfwiki |
| jokefire |
| mysql |
| performance_schema |
+--------------------+
[root@ops1:~] #mysql -uadmin_ssl -p -h
db3.example.com -e "show databases"
Enter password:
+--------------------+
| Database |
+--------------------+
| bacula |
| information_schema |
| jfwiki |
| jokefire |
| mysql |
| performance_schema |
+--------------------+
2nd web server:
[root@ops2:~] #mysql -uadmin_ssl -p -h
db3.example.com -e "show databases"
Enter password:
+--------------------+
| Database |
+--------------------+
| bacula |
| information_schema |
| jfwiki |
| jokefire |
| mysql |
| performance_schema |
+--------------------+
[root@ops2:~] #mysql -uadmin_ssl -p -h
db4.example.com -e "show databases"
Enter password:
+--------------------+
| Database |
+--------------------+
| bacula |
| information_schema |
| jfwiki |
| jokefire |
| mysql |
| performance_schema |
+--------------------+
From the 3rd web server:
[root@ops3:~] #mysql -uadmin_ssl -p -h
db3.example.com -e "show databases"
Enter password:
+--------------------+
| Database |
+--------------------+
| bacula |
| information_schema |
| jfwiki |
| jokefire |
| mysql |
| performance_schema |
+--------------------+
[root@ops3:~] #mysql -uadmin_ssl -p -h
db4.example.com -e "show databases"
Enter password:
+--------------------+
| Database |
+--------------------+
| bacula |
| information_schema |
| jfwiki |
| jokefire |
| mysql |
| performance_schema |
+--------------------+
And of course I can connect to the database master VIP from all 3 web
servers:
From 1st web server:
[root@ops1:~] #mysql -uadmin_ssl -p -h
db.example.com -e "show databases"
Enter password:
+--------------------+
| Database |
+--------------------+
| bacula |
| certs |
| information_schema |
| jf_wiki |
| jfwiki |
| jokefire |
| mysql |
| performance_schema |
+--------------------+
2nd web server:
[root@ops2:~] #mysql -uadmin_ssl -p -h
db.example.com -e "show databases"
Enter password:
+--------------------+
| Database |
+--------------------+
| bacula |
| certs |
| information_schema |
| jf_wiki |
| jfwiki |
| jokefire |
| mysql |
| performance_schema |
+--------------------+
3rd web server:
[root@ops3:~] #mysql -uadmin_ssl -p -h
db.example.com -e "show databases"
Enter password:
+--------------------+
| Database |
+--------------------+
| bacula |
| information_schema |
| jfwiki |
| jokefire |
| mysql |
| performance_schema |
+--------------------+
So what can I do to contact my database via an SSL server using the array?
Thanks,
Tim
--
GPG me!!
gpg --keyserver
pool.sks-keyservers.net --recv-keys F186197B