Changes between Version 37 and Version 38 of InstallationGuidelines/MySQL


Ignore:
Timestamp:
09/06/10 19:17:20 (14 years ago)
Author:
Fran Boon
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • InstallationGuidelines/MySQL

    v37 v38  
    5454}}}
    5555
    56 == Optimisation ==
     56=== Optimisation ===
    5757For Production instances, give the MySQL database plenty of RAM:
    5858{{{
    … …  
    7979Possibly useful tips:
    8080 * http://20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/
     81
     82=== Replication ===
     83In order to have a backup instance, configure replication from the Master node to a Backup node:
     84 * http://www.debiantutorials.net/mysql-database-replication-masterslave/
     85 * http://www.howtoforge.com/how-to-repair-mysql-replication
     86
     87==== On Master: ====
     88{{{
     89pico /etc/mysql/my.cnf
     90#bind-address = 127.0.0.1
     91server-id = 1
     92log_bin = /var/log/mysql/mysql-bin.log
     93binlog_do_db = sahana
     94
     95/etc/init.d/mysql restart
     96
     97mysql -u root -p
     98GRANT REPLICATION SLAVE ON sahana.* to username@'sl.av.e.ip' IDENTIFIED BY 'mypassword';
     99FLUSH PRIVILEGES;
     100SHOW MASTER STATUS;
     101}}}
     102Read log filename & position for configuring the slave (see below).
     103
     104==== On Slave: ====
     105{{{
     106pico /etc/mysql/my.cnf
     107server-id = 2
     108master-host = ma.st.er.ip
     109master-user = username
     110master-password = mypassword
     111master-connect-retry = 60
     112replicate-do-db = sahana
     113
     114/etc/init.d/mysql restart
     115
     116mysql -u root -p
     117
     118SLAVE STOP;
     119CHANGE MASTER TO MASTER_HOST='ma.st.er.ip', MASTER_USER='username', MASTER_PASSWORD='mypassword', MASTER_LOG_FILE='log_file', MASTER_LOG_POS=log_position;
     120SLAVE START;
     121
     122crontab -e
     123# m h  dom mon dow   command
     12455 * * * * /home/instance/scripts/automysqlbackup
     12555 * * * * /home/instance/scripts/sync-dir
     126}}}
     127
     128==== IP Failover ====
     129Can configure the slave to adopt the same IP as the master using a Heartbeat:
     130 * http://www.srg.cs.hku.hk/srg/html/cprobe/ha/GettingStarted.html
     131
     132If using Slicehost:
     133 * http://articles.slicehost.com/2008/10/28/ip-failover-slice-setup-and-installing-heartbeat
     134
     135
    81136
    82137== Administration ==
    … …  
    126181Can view the list of expensive queries by editing /etc/mysql/my.cnf and uncommenting/adding:
    127182{{{
    128 log_slow_queries        = /var/log/mysql/mysql-slow.log
     183log_slow_queries = /var/log/mysql/mysql-slow.log
    129184long_query_time = 2
    130185}}}
    … …  
    132187
    133188This will log queries that take longer than 2 seconds to run. View with:
    134 
    135189{{{
    136190tail -f /var/log/mysql/mysql-slow.log
    … …  
    139193----
    140194By installing 'mytop', you can see real-time status on the database:
    141  
     195
    142196{{{
    143197sudo apt-get install mytop
    … …  
    147201type 'o' to force the longest-running queries to the top of the screen.
    148202----
    149 Outside of this, the usual tools for monitoring memory/cpu usage is effective.
    150 
    151 Note that high mysql cpu usage may indicate a high io-wait condition, as many programs
    152 do not report iowait seperately from user / sys cpu usage. This hides the real issue. top is your friend.
     203Outside of this, the usual tools for monitoring memory/cpu usage are effective.
     204
     205Note that high mysql cpu usage may indicate a high io-wait condition, as many programs do not report iowait seperately from user / sys cpu usage. This hides the real issue. top is your friend.
    153206(iowait is time spent reading/writing to io; in this case, disk).
    154207High iowait can occur when attempting to view or sort massive quantities of data in eden.
    155208Using the above tips for increasing mysql ram usage will give mysql space to do sorts in memory.
    156209
    157 ---
    158 == Replication ==
    159 In order to have a backup instance, configure replication from the Master node to a Backup node:
    160  * http://www.debiantutorials.net/mysql-database-replication-masterslave/
    161  * http://www.howtoforge.com/how-to-repair-mysql-replication
    162 
    163 === On Master: ===
    164 {{{
    165 pico /etc/mysql/my.cnf
    166 #bind-address = 127.0.0.1
    167 server-id = 1
    168 log_bin = /var/log/mysql/mysql-bin.log
    169 binlog_do_db = sahana
    170 
    171 /etc/init.d/mysql restart
    172 
    173 mysql -u root -p
    174 GRANT REPLICATION SLAVE ON sahana.* to username@'sl.av.e.ip' IDENTIFIED BY 'mypassword';
    175 FLUSH PRIVILEGES;
    176 SHOW MASTER STATUS;
    177 }}}
    178 Read log filename & position for configuring the slave (see below).
    179 
    180 === On Slave: ===
    181 {{{
    182 pico /etc/mysql/my.cnf
    183 server-id = 2
    184 master-host = ma.st.er.ip
    185 master-user = username
    186 master-password = mypassword
    187 master-connect-retry = 60
    188 replicate-do-db = sahana
    189 
    190 /etc/init.d/mysql restart
    191 
    192 mysql -u root -p
    193 
    194 SLAVE STOP;
    195 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;
    196 SLAVE START;
    197 
    198 crontab -e
    199 # m h  dom mon dow   command
    200 55 * * * * /home/instance/scripts/automysqlbackup
    201 55 * * * * /home/instance/scripts/sync-dir
    202 }}}
    203 
    204 === IP Failover ===
    205 Can configure the slave to adopt the same IP as the master using a Heartbeat:
    206  * http://www.srg.cs.hku.hk/srg/html/cprobe/ha/GettingStarted.html
    207 
    208 If using Slicehost:
    209  * http://articles.slicehost.com/2008/10/28/ip-failover-slice-setup-and-installing-heartbeat
    210210
    211211----