Install MySQL Community Server on Ubuntu and its flavours using APT Repository with lower case table names

Author: Gautam Dayal

Published On: Tuesday, May 21, 2024

Follow the following steps to install MySQL Community Server on Ubuntu and its flavours using APT Repository and then configure to use lower case table names

1. First download MySQL APT Repository from https://dev.mysql.com/downloads/repo/apt/

2. Install the debian package using the following command: (replace the {path} and {file name} with the downloaded file path and name)

sudo dpkg -i /{path}/{file name}.deb

3. Run the update command:

sudo apt update

4. Install MySQL Community Server:

sudo apt install mysql-server

5. Post installation is complete, run the following command to check the status of the service:

systemctl status mysql

6. Install MySQL Server Workbench:

sudo apt install mysql-workbench-community

Follow the following steps to configure lower case table names:

7. Stop the MySQL service:

sudo service mysql stop

8. Delete the MySQL data directory:

sudo rm -rf /var/lib/mysql

9. Re-create MySQL data directory and give the required permissions:

sudo mkdir /var/lib/mysql
sudo chown mysql:mysql /var/lib/mysql
sudo chmod 700 /var/lib/mysql

10. First open the mysqld.cnf file in nano editor:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

11. Add lower_case_table_names = 1 to [mysqld] section in /etc/mysql/mysql.conf.d/mysqld.cnf file. Press Ctrl + O then press enter and then press Ctrl + X to exit

12. Now re-initialise MySQL with --lower_case_table_names=1 flag:

sudo mysqld --defaults-file=/etc/mysql/my.cnf --initialize --lower_case_table_names=1 --user=mysql --console

13. Start MySQL service:

sudo service mysql start

14. Retrieve the newly generated password for MySQL root user:

sudo grep 'temporary password' /var/log/mysql/error.log

15. Login to MySQL using the temporary password:

mysql -u root -p

16. Once logged in change the root password:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_root_password';

17. Then exit the MySQL shell:

exit;

18. Run MySQL secure installation command:

sudo mysql_secure_installation