mysql_secure_installation - MariaDB Knowledge Base
From MariaDB 10.4.6, mariadb-secure-installation is a symlink to mysql_secure_installation.
From MariaDB 10.5.2, mysql_secure_installation is the symlink, and mariadb-secure-installation the binary name.
mysql - ERROR 1698 (28000): Access denied for user 'root'@'localhost' - Stack Overflow
Some systems like Ubuntu, mysql is using by default the UNIX auth_socket plugin.
Basically means that: db_users using it, will be "auth" by the system user credentias. You can see if your root user is set up like this by doing the following:
$ sudo mysql -u root # I had to use "sudo" since is new installation
mysql> USE mysql;
mysql> SELECT User, Host, plugin FROM mysql.user;
+------------------+-----------------------+
| User | plugin |
+------------------+-----------------------+
| root | auth_socket |
| mysql.sys | mysql_native_password |
| debian-sys-maint | mysql_native_password |
+------------------+-----------------------+
As you can see in the query, the root user is using the auth_socket plugin
There are 2 ways to solve this:
mysql_native_password plugindb_user with you system_user (recommended)Option 1:
$ sudo mysql -u root # I had to use "sudo" since is new installation
mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;
$ sudo service mysql restart
Option 2: (replace YOUR_SYSTEM_USER with the username you have)
$ sudo mysql -u root # I had to use "sudo" since is new installation
mysql> USE mysql;
mysql> CREATE USER 'YOUR_SYSTEM_USER'@'localhost' IDENTIFIED BY 'YOUR_PASSWD';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'YOUR_SYSTEM_USER'@'localhost';
mysql> UPDATE user SET plugin='auth_socket' WHERE User='YOUR_SYSTEM_USER';
mysql> FLUSH PRIVILEGES;
mysql> exit;
$ sudo service mysql restart
Remember that if you use option #2 you'll have to connect to mysql as your system username (mysql -u YOUR_SYSTEM_USER)
Note: On some systems (e.g., Debian stretch) 'auth_socket' plugin is called 'unix_socket', so the corresponding SQL command should be: UPDATE user SET plugin='unix_socket' WHERE User='YOUR_SYSTEM_USER';
Update: from @andy's comment seems that mysql 8.x.x updated/replaced the auth_socket for caching_sha2_password I don't have a system setup with mysql 8.x.x to test this, however the steps above should help you to understand the issue. Here's the reply:
One change as of MySQL 8.0.4 is that the new default authentication plugin is 'caching_sha2_password'. The new 'YOUR_SYSTEM_USER' will have this auth plugin and you can login from the bash shell now with "mysql -u YOUR_SYSTEM_USER -p" and provide the password for this user on the prompt. No need for the "UPDATE user SET plugin" step. For the 8.0.4 default auth plugin update see, https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/
No need of sudo
The database is initialised with 2 all-privilege accounts: the first one is "root" which is inaccessible and the second one with your user name (check with command whoami).
To enable access to root account, you need to login with your user name
mysql -u $(whoami)
and manually change password for root
use mysql;
set password for 'root'@'localhost' = password('YOUR_ROOT_PASSWORD_HERE');
flush privileges;
quit
Login as 'root'
mysql -u root -pHow to Completely Remove Mysql and Install MariaDB 10 (linoxide.com)
We first need to properly uninstall MySql with all its dependencies
# yum list installed | grep mysql
mysql-community-client.x86_64 5.6.37-2.el7 @mysql56-community
mysql-community-common.x86_64 5.6.37-2.el7 @mysql56-community
mysql-community-libs.x86_64 5.6.37-2.el7 @mysql56-community
mysql-community-release.noarch el7-5 installed
mysql-community-server.x86_64 5.6.37-2.el7 @mysql56-communityTo remove a package with it's dependencies , you need to install yum plugin called: remove-with-leaves. To install it type:
# yum install yum-plugin-remove-with-leavesNow to remove a package with its dependencies: yum remove package_name --remove-leaves as below
# yum remove mysql-server --remove-leaves
Loaded plugins: fastestmirror, remove-with-leaves
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-server.x86_64 0:5.6.37-2.el7 will be erased
--> Finished Dependency Resolution
removing libaio-0.3.109-13.el7.x86_64. It is not required by anything else.
removing mysql-community-client-5.6.37-2.el7.x86_64. It is not required by anything else.
removing mysql-community-libs-5.6.37-2.el7.x86_64. It is not required by anything else.
removing mysql-community-common-5.6.37-2.el7.x86_64. It is not required by anything else.
--> Running transaction check
---> Package libaio.x86_64 0:0.3.109-13.el7 will be erased
---> Package mysql-community-client.x86_64 0:5.6.37-2.el7 will be erased
---> Package mysql-community-common.x86_64 0:5.6.37-2.el7 will be erased
---> Package mysql-community-libs.x86_64 0:5.6.37-2.el7 will be erased
--> Finished Dependency Resolution
Dependencies Resolved
======================================================================================================================================================
Package Arch Version Repository Size
======================================================================================================================================================
Removing:
libaio x86_64 0.3.109-13.el7 @base 38 k
mysql-community-client x86_64 5.6.37-2.el7 @mysql56-community 89 M
mysql-community-common x86_64 5.6.37-2.el7 @mysql56-community 2.1 M
mysql-community-libs x86_64 5.6.37-2.el7 @mysql56-community 9.2 M
mysql-community-server x86_64 5.6.37-2.el7 @mysql56-community 251 MWe also need to uninstall the mysql-community-release repo
# yum remove mysql-community-release-el7-5
Loaded plugins: fastestmirror, remove-with-leaves
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-release.noarch 0:el7-5 will be erased
--> Finished Dependency ResolutionNow we need to remove MySQL default data directory /var/lib/mysql from our system. If you don't see the directory in the default location, it means that it changed to some other place which you can find in /etc/my.cnf file with variable datadir.
In our case, we will delete the /var/lib/mysql directory from the system but you can prefer to rename it in order to keep a backup of the existing files. We will completely clean up our installation directory.
# rm -rf /var/lib/mysql/# rm -rf /etc/my.cnfTo install MariaDB 10, we must add the repo on your server. We will create a MariaDB repo file under /etc/yum.repos.d/MariaDB.repo with the content below
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.5.10(10.3)/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1now we can install our packages
# yum install MariaDB-server MariaDB-client
Loaded plugins: fastestmirror, remove-with-leaves
Loading mirror speeds from cached hostfile
* base: mirrors.linode.com
* epel: mirrors.kernel.org
* extras: mirrors.linode.com
* rpmforge: mirrors.evowise.com
* updates: mirrors.linode.com
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-client.x86_64 0:10.1.26-1.el7.centos will be installed
--> Processing Dependency: MariaDB-common for package: MariaDB-client-10.1.26-1.el7.centos.x86_64
---> Package MariaDB-server.x86_64 0:10.1.26-1.el7.centos will be installed
--> Processing Dependency: galera for package: MariaDB-server-10.1.26-1.el7.centos.x86_64
--> Running transaction check
---> Package MariaDB-common.x86_64 0:10.1.26-1.el7.centos will be installed
---> Package galera.x86_64 0:25.3.20-1.rhel7.el7.centos will be installed
--> Finished Dependency Resolution
Dependencies Resolved
======================================================================================================================================================
Package Arch Version Repository Size
======================================================================================================================================================
Installing:
MariaDB-client x86_64 10.1.26-1.el7.centos mariadb 39 M
MariaDB-server x86_64 10.1.26-1.el7.centos mariadb 103 M
Installing for dependencies:
MariaDB-common x86_64 10.1.26-1.el7.centos mariadb 123 k
galera x86_64 25.3.20-1.rhel7.el7.centos mariadb 8.0 M
Transaction Summary
======================================================================================================================================================
Install 2 Packages (+2 Dependent packages)
Total size: 150 M
Total download size: 150 M
Installed size: 647 M
Is this ok [y/d/N]: y
Downloading packages:
(1/3): MariaDB-10.1.26-centos7-x86_64-client.rpm | 39 MB 00:00:02
(2/3): galera-25.3.20-1.rhel7.el7.centos.x86_64.rpm | 8.0 MB 00:00:01
(3/3): MariaDB-10.1.26-centos7-x86_64-server.rpm | 103 MB 00:00:12
------------------------------------------------------------------------------------------------------------------------------------------------------
Total 12 MB/s | 150 MB 00:00:12
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : MariaDB-common-10.1.26-1.el7.centos.x86_64 1/4
Installing : MariaDB-client-10.1.26-1.el7.centos.x86_64 2/4
Installing : galera-25.3.20-1.rhel7.el7.centos.x86_64 3/4
Installing : MariaDB-server-10.1.26-1.el7.centos.x86_64 You can check the status as below
# yum list installed | grep mariadb
MariaDB-client.x86_64 10.1.26-1.el7.centos @mariadb
MariaDB-common.x86_64 10.1.26-1.el7.centos @mariadb
MariaDB-server.x86_64 10.1.26-1.el7.centos @mariadb
MariaDB-shared.x86_64 10.1.26-1.el7.centos @mariadb You can't have MySQL and MariaDB installed on the same server. So you need to do a complete uninstallation. You must notice that when having MariaDB installed, if you need to remove it in order to install MySQL, don't forget to remove the repo otherwise MySQL will be not able to be installed.
https://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes
run this query before your query:
SET @@global.innodb_large_prefix = 1;
this will increase limit to 3072 bytes.
# mysql -u root -p --default-character-set=utf8 databasename
mysql> SET names 'utf8'
mysql> SOURCE bkp.sqlutf8mb4 rather than utf8. Confusingly, utf8 is a flawed UTF-8 implementation from early MySQL versions which remains only for backward compatibility. The fixed version was given the name utf8mb4.utf8mb4_0900_ai_ci for equivalent rules based on Unicode 9.0 - and with no equivalent _general variant. People reading this now should probably use one of these newer collations instead of either _unicode or _general. Much of what's written below is not of much interest anymore if you can use one of the newer collations instead.utf8mb4_unicode_ci is based on the official Unicode rules for universal sorting and comparison, which sorts accurately in a wide range of languages.utf8mb4_general_ci is a simplified set of sorting rules which aims to do as well as it can while taking many short-cuts designed to improve speed. It does not follow the Unicode rules and will result in undesirable sorting or comparison in some situations, such as when using particular languages or characters.utf8mb4_unicode_ci over utf8mb4_general_ciutf8mb4_unicode_ci, which uses the Unicode rules for sorting and comparison, employs a fairly complex algorithm for correct sorting in a wide range of languages and when using a wide range of special characters. These rules need to take into account language-specific conventions; not everybody sorts their characters in what we would call 'alphabetical order'.utf8mb4_general_ci sorting in MySQL, but there are still a few differences:utf8mb4_general_ci sorts them as single characters (presumably like "s" and "e" respectively).utf8mb4_unicode_ci handles these properly.utf8mb4_general_ci sorting. The suitability of utf8mb4_general_ci will depend heavily on the language used. For some languages, it'll be quite inadequate.utf8mb4_general_ci anymore, as we have left behind the point where CPU speed is low enough that the performance difference would be important. Your database will almost certainly be limited by other bottlenecks than this.utf8mb4_general_ci except when accurate sorting was going to be important enough to justify the performance cost. Today, that performance cost has all but disappeared, and developers are treating internationalization more seriously.utf8mb4_general_ci is a compromise that's probably not needed for speed reasons and probably also not suitable for accuracy reasons.ci is for case-insensitive sorting and comparison. This means it's suitable for textual data, and case is not important. The other types of collation are cs (case-sensitive) for textual data where case is important, and bin, for where the encoding needs to match, bit for bit, which is suitable for fields which are really encoded binary data (including, for example, Base64). Case-sensitive sorting leads to some weird results and case-sensitive comparison can result in duplicate values differing only in letter case, so case-sensitive collations are falling out of favor for textual data - if case is significant to you, then otherwise ignorable punctuation and so on is probably also significant, and a binary collation might be more appropriate.unicode or general refers to the specific sorting and comparison rules - in particular, the way text is normalized or compared. There are many different sets of rules for the utf8mb4 character encoding, with unicode and general being two that attempt to work well in all possible languages rather than one specific one. The differences between these two sets of rules are the subject of this answer. Note that unicode uses rules from Unicode 4.0. Recent versions of MySQL add the rulesets unicode_520 using rules from Unicode 5.2, and 0900 (dropping the "unicode_" part) using rules from Unicode 9.0.utf8mb4 is of course the character encoding used internally. In this answer I'm talking only about Unicode based encodings.