Deploy high-performance MySQL on Block Storage

Manual deployment

To manually deploy the MySQL service, first purchase an ECS instance and an ESSD cloud disk. Then, run commands on the ECS instance to deploy the MySQL service.

Procedure

1. Configure the ECS instance as follows:

   • Instance type: ecs.c6.2xlarge.

   • This topic uses CentOS 7.9 as an example. Adjust the commands based on your operating system.

   • Mount an ESSD cloud disk as the data disk. This topic uses a 500 GiB ESSD PL1 cloud disk as an example.

   • Ensure that MySQL is not already installed on the ECS instance.

   For more information, see Create a custom instance.

2. Connect to the ECS instance.

   For more information, see Use Workbench to log on to a Linux instance.

3. Install MySQL.

   1. Run the following commands to download the MySQL installation package. This example uses MySQL 8.0.

      cd /tmp/
      wget http://mirrors.cloud.aliyuncs.com/mysql/MySQL-8.0/mysql-8.0.27-1.el7.x86_64.rpm-bundle.tar

   2. Run the following command to extract the installation package.

      tar -xf mysql-8.0.27-1.el7.x86_64.rpm-bundle.tar

   3. Run the following command to install MySQL.

      sudo yum install -y mysql-community-{server,client,common,libs,devel}-*

   4. Run the following command to stop the MySQL process.

      sudo systemctl stop mysqld

4. Initialize the cloud disk.

   1. Run the following command to obtain the device name of the data disk.

      sudo fdisk -l

      The following output shows that the device name of the uninitialized data disk is vdb.

      [ecs-user@iZxxx Z tmp]$ sudo fdisk -l
      Disk /dev/vda: 42.9 GB, 42949672960 bytes, 83886080 sectors
      Units = sectors of 1 * 512 = 512 bytes
      Sector size (logical/physical): 512 bytes / 512 bytes
      I/O size (minimum/optimal): 512 bytes / 512 bytes
      Disk label type: dos
      Disk identifier: 0x000c2760
         Device Boot      Start         End      Blocks   Id  System
      /dev/vda1   *        2048    83886046    41941999+  83  Linux
      Disk /dev/vdb: 42.9 GB, 42949672960 bytes, 83886080 sectors
      Units = sectors of 1 * 512 = 512 bytes
      Sector size (logical/physical): 512 bytes / 512 bytes
      I/O size (minimum/optimal): 512 bytes / 512 bytes

   2. Run the following command to set a global environment variable.

      DEV_LABEL=vdb

      Replace vdb with the device name of your data disk.

   3. Run the following command to set the MySQL data path.

      MYSQL_HOME=/home/ecs-user/${DEV_LABEL}

   4. Run the following command to prevent virtio from splitting I/O requests.

      sudo sh -c 'echo 256 >/sys/block/vdb/queue/max_sectors_kb'

   5. Run the following command to create the MySQL directory.

      if [ ! -d ${MYSQL_HOME} ]; then
       sudo mkdir -p ${MYSQL_HOME}
      fi

   6. If the data disk is already mounted, run the following command to unmount it.

      If it is not mounted, skip this step.

      sudo umount /dev/${DEV_LABEL}

   7. Run the following command to format the file system, enable the bigalloc option, and set the file system block size to 16 KB.

      echo y | sudo mkfs.ext4 -O bigalloc -C 16k /dev/${DEV_LABEL}

   8. Run the following command to mount the data disk.

      sudo mount /dev/${DEV_LABEL} ${MYSQL_HOME}

   9. Run the following command to create the required MySQL directories.

      sudo mkdir -p ${MYSQL_HOME}/data/dbs
      sudo mkdir -p ${MYSQL_HOME}/data/mysql
      sudo mkdir -p ${MYSQL_HOME}/log/mysql
      sudo mkdir -p ${MYSQL_HOME}/log/redo

5. Configure MySQL.

   1. Run the following command to create and populate the MySQL configuration file (/etc/my.cnf).

      sudo tee /etc/my.cnf <<EOF
      [mysqld]
      port = 3306
      back_log = 3000
      datadir = ${MYSQL_HOME}/data/dbs
      log-bin = ${MYSQL_HOME}/log/mysql/mysql-bin.log
      log-error = ${MYSQL_HOME}/log/mysql/master-error.log
      innodb_data_home_dir = ${MYSQL_HOME}/data/mysql/
      innodb_log_group_home_dir = ${MYSQL_HOME}/log/redo/
      innodb_doublewrite_dir = ${MYSQL_HOME}/doublewrite
      character_set_server = utf8
      sync_binlog = 1000
      innodb_doublewrite = 0
      max_connections=151
      max_allowed_packet=64M
      innodb_buffer_pool_size=128M
      innodb_buffer_pool_instances=1
      innodb_read_io_threads=4
      innodb_write_io_threads=4
      innodb_buffer_pool_instances=1
      innodb_flush_log_at_trx_commit=1
      innodb_flush_method=O_DIRECT
      default_authentication_plugin = mysql_native_password
      EOF

      For more information about MySQL parameters, see MySQL parameters.

   2. Run the following command to initialize MySQL.

      sudo mysqld --defaults-file=/etc/my.cnf --initialize

6. Start MySQL.

   1. Run the following command to start MySQL.

      sudo mysqld --user=root --daemonize

   2. Run the following command to verify that MySQL has started.

      ps -aux | grep mysqld

      The following output indicates that MySQL has started.

      [ecs-user@iZbp...                    ~]$ ps -aux | grep mysqld
      root     17144  0.1  4.6 1794466 365248 ?      S1   14:03   0:01 mysqld --user=root --daemonize
      ecs-user 17223  0.0  0.8 112816   976 pts/1    S+   14:18   0:00 grep --color=auto mysqld

7. (Optional) Configure MySQL to start on boot.

   1. Run the following command to go to the target directory. If the directory does not exist, use mkdir to create it.

      cd /etc/rc.d/init.d

   2. Run the following command to create a startup script.

      sudo tee /etc/rc.d/init.d/ebs_mysql_16k_auto_start.sh <<EOF
      #!/bin/bash
      # chkconfig: 345 20 80
      # description: EBS MySQL 16K Auto Start Script
      start() {
          DEV_LABEL=vdb
          MYSQL_HOME=/home/ecs-user/\${DEV_LABEL}
          echo 256 >/sys/block/vdb/queue/max_sectors_kb
          umount /dev/\${DEV_LABEL}
          mount /dev/\${DEV_LABEL} \${MYSQL_HOME}
          mysqld --user=root --daemonize
      }
      stop() {
          killall mysqld
          echo "Stopping mysqld..."
      }
      restart() {
          stop
          sleep 2
          start
      }
      case "\$1" in
          start)
              start
              ;;
          stop)
              stop
              ;;
          restart)
              restart
              ;;
          *)
              echo "Usage: \$0 {start|stop|restart}"
              exit 1
      esac
      exit 0
      EOF

   3. Run the following command to make the script executable.

      sudo chmod +x /etc/rc.d/init.d/ebs_mysql_16k_auto_start.sh

   4. Run the following command to add the script as a startup task.

      sudo chkconfig --add ebs_mysql_16k_auto_start.sh

   5. Run the following command to enable the script to start on boot.

      sudo chkconfig ebs_mysql_16k_auto_start.sh on

One-click deployment in Labs

You can use the one-click deployment feature in the Elastic Block Storage (EBS) Labs to deploy the MySQL service without manually purchasing resources or building the environment. This lab scenario uses the automatic orchestration and deployment capabilities of Resource Orchestration Service (ROS) and combines the 16K atomic write capability of ESSD cloud disks with MySQL use cases for a simpler, hands-on MySQL experience.

1. Go to the Lab scenario page.

   1. Log on to the Elastic Block Storage (EBS) console.

      If you are logging on to the EBS console for the first time, follow the on-screen instructions to create an EBS service-linked role. For more information, see Service-linked role for EBS.

   2. In the left-side navigation pane, choose Laboratory > Lab scenario.

   3. In the upper-left corner of the top menu bar, select a region.

   4. On the Deploy high-performance MySQL and a comparison environment based on EBS template, click One-click creation of experiment tasks.

2. On the Create lab task wizard page, configure the basic information for the lab scenario and click Next.

   Parameter	Description
   Lab scenario	The default value is Deploy a High-performance MySQL Service Based on EBS.
   Lab task name	A default name is provided, which you can customize.
   Description	(Optional) Enter a description for the lab task.

3. On the dependency check page, review the dependency check results. When No error found. appears, click Next.

4. On the Resource parameters page, configure the resource parameters and click Next.

   Parameter	Description
   Zone	Select the availability zone for the instance. The example uses cn-hangzhou-g.
   ECS instance type	Select an instance type. This example uses ecs.c6.2xlarge, which provides 8 vCPUs and 16 GiB of memory.
   Instance password	A default value is provided. You can modify it as needed. Important Securely store the instance password. This password is required to connect to the ECS instance.
   System disk type	Select a system disk type. This example uses cloud_essd.
   The system disk size.	Specify the system disk size as needed. The example uses 40 GiB.
   Data disk type	Only cloud_essd (ESSD PL1 cloud disk) is supported.
   Data disk performance level	Only PL1 is supported.
   Data disk size	Specify the data disk size as needed. The example uses 500 GiB.
   Database password	A default value is provided. You can modify it as needed. Important Securely store the database password. It is required to connect to MySQL.
   Database port	The MySQL server listens on port 3306. To access the MySQL database, you must also open port 3306 in the security group of the ECS instance. For more information, see Add a security group rule.

5. Review the configuration parameters and billing information, and then click OK. In the Create lab task dialog box, click OK.

   After the lab task is created, the system prepares the resources and builds the lab scenario. This process takes about 5 minutes.

   Lab tasks rely on the orchestration capabilities of ROS. Each lab task corresponds to one stack. You can create a maximum of 200 lab tasks.

6. When the task status changes to Created, click the Lab task ID to view the task configuration details.