Configuring MySQL server

The MySQL server, mysqld, has many command options and system variables that can be set at startup to configure its operation. The configurations are stored in the my.cnf file.

  1. SSH into the database server host as a root user.
  2. Open the my.cnf file for editing in an editor such as vi or nano:
    On RHEL/CentOS/SLES:
    vi /etc/my.cnf
    On Debian/Ubuntu:
    vi /etc/mysql/my.cnf
  3. Ensure that the bind-address property is set to 0.0.0.0 or is commented out if case of a default value.
    bind-address=0.0.0.0
  4. Ensure that the default-storage-engine property is set to innodb, which is the default storage engine in MySQL versions 5.5 and higher.
    default-storage-engine=innodb
  5. Ensure that the sql_mode property is set to STRICT_ALL_TABLES to avoid columns being truncated during migration.
    sql_mode=STRICT_ALL_TABLES
  6. Save the file and exit.
  7. Restart the database server.
  8. Enable the server to automatically start on boot:
    On RHEL/CentOS:
    sudo chkconfig mysqld on
    On SLES:
    sudo chkconfig mysql on
    sudo rcmysql status
    On Ubuntu, MySQL starts automatically after installation. Run the following command to check the status:
    sudo service mysql status
    On Debian, MySQL starts automatically after installation. Run the following command to check the status:
    sudo service mysql status