Changes between Version 7 and Version 8 of InstallationGuidelines/MySQL

Timestamp:

06/04/10 12:33:03 (14 years ago)

Author:

Fran Boon

Comment:

Optimisation, Replication

InstallationGuidelines/MySQL

@@ -2 +2 @@
 Replacing the simple SQLite database with MySQL allows greater scalability
 
-'''Note''' : [[BR]]
+==== Notes ====
+ 1. The databases directory contains what web2py knows about your database
+ 2. You need to set Migrate=True in {{{models/00_db.py}}} before you update the code and switch back to Migrate=False later on.
+ 3. In case of database mismatches - check the databases directory for the appropriate table ( cat the file ) and change accordingly.
 
-1) The databases directory contains what web2py knows about your database[[BR]]
-2) You need to set Migrate=True in 00_db.py before you update the code and switch back to Migrate = False later on.[[BR]]
-3) In case of database mismatches - check the databases directory for the appropriate table ( cat the file ) and change accordingly.[[BR]]
-
-
-Install Python support for MySQL:
+=== Install Python support for MySQL ===
 {{{
 apt-get install python-mysqldb
@@ -21 +19 @@
 }}}
 
-Create the database 1st:
+=== Create the database ===
+Database should be created within MySQL before Web2Py can see it to add the relevant tables:
 {{{
 mysqladmin --user=root -p create sahana
@@ -28 +27 @@
 }}}
 
+=== Configure Eden ===
 Suggest to use connection pools to reuse connections in {{{models/00_db.py}}}:
 {{{
@@ -33 +33 @@
 }}}
 
-'''How many pools?'''
+==== How many pools? ====
 
-Whatever you choose, it always starts with one and grows with the
-number of concurrent requests up to the value of pools (the max number
-of concurrent pools). So pools should be the max number of concurrent
-requests you expect. That is the max value of:
-<number of requests/second> / <average time per request in seconcds> 
+Whatever you choose, it always starts with one and grows with the number of concurrent requests up to the value of pools (the max number of concurrent pools). So pools should be the max number of concurrent requests you expect. That is the max value of:
+{{{
+<number of requests/second> / <average time per request in seconds>
+}}}
+
+=== MySQL optimisation ===
+For Production instances, give the MySQL database plenty of RAM:
+{{{
+innodb_buffer_pool_size = 2048M
+innodb_file_per_table
+innodb_thread_concurrency = 8
+}}}
+
+=== MySQL Replication ===
+In order to have a backup instance, configure replication from the Master node to a Backup node:
+ * http://www.debiantutorials.net/mysql-database-replication-masterslave/
+On Master: 
+{{{
+pico /etc/mysql/my.cnf
+#bind-address = 127.0.0.1
+server-id = 1
+log_bin = /var/log/mysql/mysql-bin.log
+binlog_do_db = sahana
+
+/etc/init.d/mysql restart
+
+mysql -u root -p
+GRANT REPLICATION SLAVE ON sahana.* to username@'sl.av.e.ip' IDENTIFIED BY 'mypassword';
+FLUSH PRIVILEGES;
+SHOW MASTER STATUS;
+}}}
+Read log filename & position for configuring the slave (see below).
+
+On Slave: 
+{{{
+pico /etc/mysql/my.cnf
+server-id = 2
+master-host = ma.st.er.ip
+master-user = username
+master-password = mypassword
+master-connect-retry = 60
+replicate-do-db = sahana
+
+/etc/init.d/mysql restart
+
+mysql -u root -p)
+
+SLAVE STOP;
+CHANGE MASTER TO MASTER_HOST='ma.st.er.ip', MASTER_USER='username', MASTER_PASSWORD='mypassword', MASTER_LOG_FILE='log_file', MASTER_LOG_POS=log_position;
+SLAVE START;
+}}}
+
+==== IP Failover ====
+Can configure the slave to adopt the same IP as the master using a Heartbeat.
+
+If using Slicehost:
+ * http://articles.slicehost.com/2008/10/28/ip-failover-slice-setup-and-installing-heartbeat
 
 ----