How to reinstall MySQL server?

June

2023

Linux

5 June 2023 13:35

The need to reinstall the MySQL server arises when updating its version.

Why update MySQL server

The main difference, except version (8.0.33) - you can install the latest version to it ODBC Connector. Since there are no problems with the dependencies of packages.

MS SQL installation requirements

To fulfill this instruction we need:

1) Linux
2) the password of the ROOT user of the operating system of this computer, or you must be in the SUDO, Sudoers or Wheels group of this PC (see Groups command).
3) The Root user password in the MS SQL database management system (but you can drop it - see the next section).
4) free space - about 0.5 GB for the database.

If the Root password in MySQL is not known or forgotten

The following steps will help reset the Root password:

1) Stop the MySQL

sudo service mysql stop

2) Launch MySQL in a safe monopoly mode

sudo mkdir /var/run/mysqld
sudo chmod 1777 /var/run/mysqld;
sudo mysqld_safe --skip-grant-tables

Or fix the file /etc/Mysql/conf.d/mysql.cnf-Delete a comment # before the line # Skip-grant-tables
and restart the Sudo Service MySQL Start service

From another terminal window

sudo mysql -u root

For MySQL 8.0 and above, we perform MySQL commands:

use mysql; Update User Set Authentication_String = Concat ('\*', Upper (Sha1 (UNHEX (SHA1 ('11111')))) WHERE User = 'ROOT'; Update User Set Plugin = "MySQL_NATIVE_PASSWORD" WHERE User = 'ROOT'; Flush Privileges; QUIT We complete the first window (where the Sudo MySQLD_SAFE-GRANT-Tables) team operates And then we complete the MySQLD process: sudo pkill mysqld Service launch: sudo service mysql start Examination: mysql -u root -p -h localhost `11111` ### 1) backup backup (Backup DB) Official Backup-And-Racovery Documentation [Here](https://dev.mysql.com/doc/refman/8.0/en/backup-and-recovery.html). Find out the database names: mysql -u root -p -h localhost show databases; We make backup (data export) from the database to text files: mysqldump -u root -p -h localhost primer > primer.sql mysqldump -u root -p -h localhost test > test.sql mysqldump -u root -p -h localhost db_1 > db_1.sql The program will request a Root password for MySQL. Enter the password 11111 (for Root). ### 2) removal of the previous MySQL installation Remove Mysql client: sudo apt-get purge mysql-client mysql-client-8.0 mysql-client-core-8.0 After backing up the database, you can delete the MySQL server [instructions](https://learnubuntu.com/uninstall-mysql/) sudo apt purge mysql-server mysql-common* mysql-server-5.7* mysql-server-8.0* Remove the configures sudo rm -r /etc/mysql /var/lib/mysql /var/log/mysql Clear sudo apt autoclean && sudo apt autoremove When the server is removed, a window will be displayed with a request for removing the database - you need to agree. ### 3) Installing a copy of the new version of MySQL server according to the instructions for 'Apt' Official Installation Instructions MySQL [Here](https://dev.mysql.com/doc/refman/8.0/en/linux-installation.html). I chose the option with APT - Instruction [Here](https://dev.mysql.com/doc/refman/8.0/en/linux-installation-apt-repo.html) 1) We load the Repository Setup Package package (17 KB) in the form of an Deb file - it does not depend on the platform From the page [https://dev.mysql.com/downloads/repo/apt/](https://dev.mysql.com/downloads/repo/apt/) Open the Deb with a double click, or perform a command, for example. dpkg -i mysql-apt-config_0.8.25-1_all.deb When starting, there will be a request to which OS you need to add APT config. Linux Mint 21.1 Vera corresponds to Ubuntu Jammy (22). For other OS, the choice will be different. We answer the questions (for the graphic method of GDEBI or in DPKG - similarly). (Pressing on the image increases the picture). To the question "Add Repository to Supported Server" - we make a choice for my version of the OS (Ubuntu / Debian) - I have "Ubuntu Jammy" ![Debi](debi.png?lightbox&classes=img-zoomable "debi") In the next step, I chose "MySQL Tools and Connectors" ![Debi2](debi2.png?lightbox&classes=img-zoomable "debi2") At the third step, "Enable or Disable MySQL Tools and Utilities" chose "Enabled", because The workstation is used for development. ![Debi3](debi3.png?lightbox&classes=img-zoomable "debi3") At the last step, I chose the "OK" item to confirm the correct data and complete the package settings. ![Debi4](debi4.png?lightbox&classes=img-zoomable "debi4") We update the repository in the system: sudo apt-get update During their update, it is clear that the files are taken from the site and "turnips" http://repo.mysql.com/apt/ubuntu Jammy inrelease I continue to fulfill [Mysql installation instructions](https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/): #### Mysql Community Server packages team, optional packages and files of the MySQL client plugin The server, optional MySQL-Common packs and MySQL-Community-Client-Plugins Plugin is preferably set by _Odnoy command_: sudo apt-get install mysql-server mysql-common mysql-community-client-plugins We set the Root password (click on the picture to increase): ![Root](root.png?lightbox&classes=img-zoomable "root") Choose the desired way to encrypt a password (click on the picture to increase): ![Root2](root2.png?lightbox&classes=img-zoomable "root2") MySQL service starts after installation automatically. Check her work: sudo systemctl status mysql To strengthen security, you can prohibit remote connections to MySQL server sudo mysql_secure_installation `Securing the MySQL server deployment.` `Enter password for user root: ` `VALIDATE PASSWORD COMPONENT can be used to test passwords` ``and improve security. It checks the strength of password` `and allows the users to set only those passwords which are` `secure enough. Would you like to setup VALIDATE PASSWORD component?` `Press y|Y for Yes, any other key for No: n` `Using existing password for root.` `Change the password for root ? ((Press y|Y for Yes, any other key for No) : n` `... skipping.` `By default, a MySQL installation has an anonymous user,` `allowing anyone to log into MySQL without having to have` `a user account created for them. This is intended only for` `testing, and to make the installation go a bit smoother.` `You should remove them before moving into a production` `environment.` `Remove anonymous users? (Press y|Y for Yes, any other key for No) : n` `... skipping.` `Normally, root should only be allowed to connect from` `'localhost'. This ensures that someone cannot guess at` `the root password from the network.` `Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y` `Success.` `By default, MySQL comes with a database named 'test' that` `anyone can access. This is also intended only for testing,` `and should be removed before moving into a production` `environment.` `Remove test database and access to it? (Press y|Y for Yes, any other key for No) : n` `... skipping.` `Reloading the privilege tables will ensure that all changes` `made so far will take effect immediately.` `Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y` `Success.` `All done! ` We check the connection with the name of the user of the database "Root": mysql -u root -p и вводим пароль: Since MySQL is launched and works, the standard invitation ** MySQL> ** will be displayed in the terminal. We write commands for creating databases: Create Database Primer; Use Primer; Source Primer.sql; etc. for each database. Team for exit from the client Mysql: quit Checql version check: mysql --version MySQL Ver 8.0.33 for Linux on X86_64 (MySQL Community Server - GPL) ### Musql connection permission via Firewall Turn on the UFW sudo ufw enable If the car is supposed to be connected from the outside to MySQL, we allow access to the MySQL to the server: sudo ufw allow mysql If the remote connections from the Localhost outside to MySQL are not needed, we prohibit remote access to the MySQL server: sudo ufw deny mysql ### Result We just updated MySQL to "MySQL Community Server"! You can connect to the MySQL server using various software interfaces such as MySQL ODBC Connector, JDBC or NATIVE MySQL or PHP using MySQLI.

Blog about Radio and Linux