Changes between Version 7 and Version 8 of InstallationGuidelines/MySQL


Ignore:
Timestamp:
06/04/10 12:33:03 (12 years ago)
Author:
Fran Boon
Comment:

Optimisation, Replication

Legend:

Unmodified
Added
Removed
Modified
  • InstallationGuidelines/MySQL

    v7 v8  
    22Replacing the simple SQLite database with MySQL allows greater scalability
    33
    4 '''Note''' : [[BR]]
     4==== Notes ====
     5 1. The databases directory contains what web2py knows about your database
     6 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.
     7 3. In case of database mismatches - check the databases directory for the appropriate table ( cat the file ) and change accordingly.
    58
    6 1) The databases directory contains what web2py knows about your database[[BR]]
    7 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]]
    8 3) In case of database mismatches - check the databases directory for the appropriate table ( cat the file ) and change accordingly.[[BR]]
    9 
    10 
    11 Install Python support for MySQL:
     9=== Install Python support for MySQL ===
    1210{{{
    1311apt-get install python-mysqldb
     
    2119}}}
    2220
    23 Create the database 1st:
     21=== Create the database ===
     22Database should be created within MySQL before Web2Py can see it to add the relevant tables:
    2423{{{
    2524mysqladmin --user=root -p create sahana
     
    2827}}}
    2928
     29=== Configure Eden ===
    3030Suggest to use connection pools to reuse connections in {{{models/00_db.py}}}:
    3131{{{
     
    3333}}}
    3434
    35 '''How many pools?'''
     35==== How many pools? ====
    3636
    37 Whatever you choose, it always starts with one and grows with the
    38 number of concurrent requests up to the value of pools (the max number
    39 of concurrent pools). So pools should be the max number of concurrent
    40 requests you expect. That is the max value of:
    41 <number of requests/second> / <average time per request in seconcds>
     37Whatever 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:
     38{{{
     39<number of requests/second> / <average time per request in seconds>
     40}}}
     41
     42=== MySQL optimisation ===
     43For Production instances, give the MySQL database plenty of RAM:
     44{{{
     45innodb_buffer_pool_size = 2048M
     46innodb_file_per_table
     47innodb_thread_concurrency = 8
     48}}}
     49
     50=== MySQL Replication ===
     51In order to have a backup instance, configure replication from the Master node to a Backup node:
     52 * http://www.debiantutorials.net/mysql-database-replication-masterslave/
     53On Master:
     54{{{
     55pico /etc/mysql/my.cnf
     56#bind-address = 127.0.0.1
     57server-id = 1
     58log_bin = /var/log/mysql/mysql-bin.log
     59binlog_do_db = sahana
     60
     61/etc/init.d/mysql restart
     62
     63mysql -u root -p
     64GRANT REPLICATION SLAVE ON sahana.* to username@'sl.av.e.ip' IDENTIFIED BY 'mypassword';
     65FLUSH PRIVILEGES;
     66SHOW MASTER STATUS;
     67}}}
     68Read log filename & position for configuring the slave (see below).
     69
     70On Slave:
     71{{{
     72pico /etc/mysql/my.cnf
     73server-id = 2
     74master-host = ma.st.er.ip
     75master-user = username
     76master-password = mypassword
     77master-connect-retry = 60
     78replicate-do-db = sahana
     79
     80/etc/init.d/mysql restart
     81
     82mysql -u root -p)
     83
     84SLAVE STOP;
     85CHANGE MASTER TO MASTER_HOST='ma.st.er.ip', MASTER_USER='username', MASTER_PASSWORD='mypassword', MASTER_LOG_FILE='log_file', MASTER_LOG_POS=log_position;
     86SLAVE START;
     87}}}
     88
     89==== IP Failover ====
     90Can configure the slave to adopt the same IP as the master using a Heartbeat.
     91
     92If using Slicehost:
     93 * http://articles.slicehost.com/2008/10/28/ip-failover-slice-setup-and-installing-heartbeat
    4294
    4395----