MySQL installation log in Tokyo

• Port Forwarding
  • At master server
    • Add new user repl
    • su repl
    • ssh-keygen -t rsa
    • mv ~/.ssh/id_rsa.pub ~/.ssh/authorized_keys
    • cp ~/.ssh/authorized_keys ~/.ssh/authorized_keys2
    • Send id_rsa to slave server
  • At slave server
    • Download autossh ver.1.2f from here
    • Extract it and copy autossh to /usr/local/bin/
    • mkdir /usr/local/mysql_forward
    • Put id_rsa whch was created at master server at /usr/local/mysql_forward
    • vi /usr/local/mysql_forward/mysqlforward.sh

      #!/bin/sh
      
      ID=repl
      HOST=pc4466.psi.ch
      
      if [ "X$SSH_AUTH_SOCK" = "X" ]; then
      eval `ssh-agent -s`
      ssh-add /usr/local/mysql_forward/id_rsa
      fi
      
      AUTOSSH_POLL=600
      AUTOSSH_PORT=20000
      AUTOSSH_GATETIME=30
      AUTOSSH_LOGLEVEL=7
      AUTOSSH_LOGFILE=$HOST.log
      AUTOSSH_DEBUG=yes
      AUTOSSH_PATH=/usr/bin/ssh
      export AUTOSSH_POLL AUTOSSH_LOGFILE AUTOSSH_DEBUG AUTOSSH_PATH AUTOSSH_GATETIME AUTOSSH_PORT
      
      /usr/local/bin/autossh -2 -fN -M 20000 -L 23306:pc4466:3306 ${ID}@${HOST}
      

• mysqlcc
  • Download mysqlcc-0.9.4-linux-glibc23.tar.gz from here
  • cd /usr/local/
  • tar xvzf mysqlcc-0.9.4-linux-glibc23.tar.gz
  • ln -s mysqlcc-0.9.4-linux-glibc23 mysqlcc
• MySQL installation
  • Download MySQL-server-4.0.20-0.i386.rpm and MySQL-client-4.0.20-0.i386.rpm from here.
  • rpm -Uvh MySQL-server-4.0.20-0.i386.rpm
  • rpm -Uvh MySQL-client-4.0.20-0.i386.rpm
• MySQL replication set up
  • At master server
    • mysql -u root -p
    • GRANT REPLICATION SLAVE ON *.* TO repl@%IDENTIFIED BY 'password';
    • FLUSH TABLES WITH READ LOCK;
    • Copy /var/lib/mysql to slave server
    • Add following lines into /etc/my.conf

      [mysqld]
      log-bin
      server-id=1
      

  • At slave server
    • Modify /etc/my.conf

      pid_file=$datadir/umemaru.icepp.s.u-tokyo.ac.jp.pid

    • Add following lines into /etc/my.conf

      [mysqld]
      log-bin
      server-id=2
      

    • Modify /etc/init.d/mysql

      $bindir/mysqld_safe --replicate-ignore-db=mysql --replicate-ignore-db=test --datadir=$datadir --pid-file=$pid_file \>/dev/null 2\>&1 &

    • Modify /etc/init.d/mysql

      pid_file=$datadir/umemaru.icepp.s.u-tokyo.ac.jp.pid

    • /etc/init.d/mysql start
    • /usr/local/mysql_forward/ mysqlforward.sh
    • mysql -u root -p

      change master to master_host='127.0.0.1',
      master_user='repl',
      master_port='23306',
      master_password='password',
      master_log_file='pc4466-bin.001',
      master_log_pos=79;
      

    • SLAVE START
    • Make all users except root readonly.
  • At master server
    • mysql -u root -p
    • UNLOCK TABLES;

  ---

  
  R.Sawada
  [Last Update: 24-Feb-2005]