1. Uninstalling Mysql

  • Use dpkg --get-selections | grep mysql to list all softwares related to Mysql, then uninstall them with sudo apt-get --purge remove NAME

    ➜  ~ dpkg --get-selections | grep mysql
    default-mysql-client             install
    libdbd-mysql-perl                install
    libmysqlclient18:amd64           deinstall
    mysqll-apt-config                install
    ......
      
    sudo apt-get --purge remove mysql-server
    sudo apt-get --purge remove mysql-apt-config
    sudo apt-get --purge remove ...
    ......
    

2. Installing Mysql

  • Installation steps link
  • E.g., mysql-apt-config_0.8.15-1_all.deb

    sudo dpkg -i /PATH/version-specific-package-name.deb
    sudo apt-get update
    sudo apt-get install mysql-server
    sudo service mysql status/stop/start
    

3. Creating a new user

  • Create a new user and grant all privileges to the new user (From link)

    // replace localhost with % to allow remote access
    mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
    mysql> GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost' (WITH GRANT OPTION);
    mysql> FLUSH PRIVILEGES;
    
  • Allow remote access

    # sudo vi /etc/mysql/my.cnf
    # comment the following line
    ...
    # bind-address          = 127.0.0.1
    ...
    

4. Hot Backup (Optional)

  • For master

    # sudo vi /etc/mysql/my.cnf
    ......
    server-id                = 1  # master
    log_bin                  = /var/log/mysql/mysql-bin.log
    expire_logs_days         = 3
    max_binlog_size          = 100M
    #binlog_do_db            = include_database_name
    #binlog_ignore_db        = include_database_name
    log-slave-updates
    sync_binlog = 1
    auto_increment_offset    = 1  # = server-id
    auto_increment_increment = 2  # the total number of servers (master + slaves)
    ......
    
  • For Slave

    ......
    server-id                = 2  # slave
    log_bin                  = /var/log/mysql/mysql-bin.log
    expire_logs_days         = 3
    max_binlog_size          = 100M
    #binlog_do_db            = include_database_name
    #binlog_ignore_db        = include_database_name
    log-slave-updates
    sync_binlog = 1
    auto_increment_offset    = 2
    auto_increment_increment = 2
    ......
    
  • Restart each DB sudo service mysql restart
  • Grant to slaves

    GRANT REPLICATION SLAVE ON *.* to 'username'@'%' identified by 'password';
    FLUSH PRIVILEGES;
    
  • Check status of DB at master/slaves

    mysql> show master status;
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000005 |    72739 |              |                  |
    +------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)
    
  • Configure salves to track the position of log in master

    # master
    change master to master_host='*',master_user='*',master_password='*',master_log_file='*',master_log_pos=*;
    start slave;
    
    # slave
    change master to master_host='*',master_user='*',master_password='*',master_log_file='*',master_log_pos=*;
    start slave;
    
  • Verify the configuration

    mysql> show slave status\G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 10.115.69.12
                      Master_User: byteflow
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000004
              Read_Master_Log_Pos: 68862
                   Relay_Log_File: mysqld-relay-bin.000002
                    Relay_Log_Pos: 493
            Relay_Master_Log_File: mysql-bin.000004
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 68862
                  Relay_Log_Space: 650
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 2
    1 row in set (0.00 sec)
    

5. TPCH Benchmark

REF

  1. Debian 9安装MySql