My cheatsheet on mysql

1 MySQL cheat commands

This is just a dump of a bunch of sql commands, no explanation. For explanations and more detailed options see sections below.

$ systemctl status mariadb  (or mysql)
$ systemctl start mariadb
$ systemctl enable mariadb
$ sudo /usr/bin/mysql_secure_installation
$ mysql -u root -p       |
$ mysql -u root -p       |
$ mysql -u root -p       |--   This is how you "connect" to mysql     
$ mysql -u root -p       |
$ mysql -u root -p       |
$ mysql -u root -p       |

Mysql> SHOW databases;
mysql> SHOW tables;
mysql> SHOW tables FROM database-name;
mysql> SHOW COLUMNS FROM table-name;

mysql> USE mysql
mysql> SHOW tables
mysql> SHOW COLUMNS FROM user;
mysql> SELECT user, host, password FROM mysql.user;

mysql> USE mysql;
mysql> SHOW tables;
mysql> SELECT user, host, password FROM user;
mysql> SELECT user, host, password FROM user;
mysql> SELECT user, host, password FROM user;
mysql> SELECT user, host, password FROM user;
mysql> SELECT user, host, password FROM user;
mysql> SELECT user, host, password FROM user;

       SELECT field1, field2..     FROM table;



mysql> UPDATE user SET authentication_string = PASSWORD('SunD@sch00l')
     > WHERE User = 'root' AND Host = 'localhost';
mysql> FLUSH PRIVILEGES;
mysql> SELECT * FROM information_schema.TABLES WHERE TABLE_NAME LIKE '%user%';

mysql> DESC user; 
mysql> USE mysql;
mysql> SELECT host, user FROM user WHERE user = 'root';
mysql> SELECT host, user FROM user WHERE host = '%';

mysql> CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypasswd';
mysql> CREATE USER 'newuser'@'192.168.111.12' IDENTIFIED BY 'mypasswd';
mysql> CREATE USER 'myuser'@'vm1.zintis.ops' IDENTIFIED BY 'mypasswd';

mysql> GRANT ALL PRIVILEGES ON  database.table TO  myuser@localhost IDENTIFIED BY 'mypasswd';
mysql> GRANT ALL PRIVILEGES ON  Finance.Sales  TO 'myuser'@'%'      IDENTIFIED BY 'mypasswd';
mysql> GRANT ALL PRIVILEGES ON  Finance.*      TO 'myuser'@'%'      IDENTIFIED BY 'mypasswd';
mysql> GRANT ALL PRIVILEGES ON  *.*            TO 'root'@'%'        IDENTIFIED BY 'password';

mysql> FLUSH PRIVILEGES
mysql> DESCRIBE column_name;
mysql> 
mysql> GRANT CREATE PRIVILEGES ON *.* TO creator@localhost;
mysql> GRANT DROP PRIVILEGES ON ...
mysql> GRANT DELETE PRIVILEGES ON ...
mysql> GRANT INSERT PRIVILEGES ON ..
mysql> GRANT SELECT PRIVILEGES ON ...
mysql> GRANT UPDATE PRIVILEGES ON ...
mysql>
mysql> GRANT INSERT, DELETE, DROP PRIVILEGES ON ...
mysql>
mysql> SHOW GRANTS FOR 'database_user'@'localhost';
mysql> REVOKE ALL PRIVILEGES ON roundcubemail.* FROM 'fired-employee'@'localhost';
mysql > =DROP USER 'user'@'localhost'=

1.1 Ummmm, what is going on at zintis.net

Database changed
MariaDB [mysql]> SELECT user, host, password FROM user;
+-----------+-----------+-------------------------------------------+
| user      | host      | password                                  |
+-----------+-----------+-------------------------------------------+
| root      | localhost | *69F4D5A6B14A573595DEE6242DBF475F588C36E2 |
| root      | 127.0.0.1 | *69F4D5A6B14A573595DEE6242DBF475F588C36E2 |
| root      | ::1       | *69F4D5A6B14A573595DEE6242DBF475F588C36E2 |
| admin     | localhost | *061ED82A6E7D5FCE6612ACD128DF5037EEEFA264 |
| wordpress | localhost | *BA777A3D5BB318426F499C26DFA937B15BEAA372 |
+-----------+-----------+-------------------------------------------+
5 rows in set (0.000 sec)

MariaDB [mysql]> UPDATE user SET authentication_string = PASSWORD('xxxxxxxxxxxx')
    -> WHERE User = 'wordpress' AND Host = 'localhost';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mysql]> SELECT user, host, password FROM user;
+-----------+-----------+-------------------------------------------+
| user      | host      | password                                  |
+-----------+-----------+-------------------------------------------+
| root      | localhost | *6AF39F4D5A9F4D56B14A95DEE6242DBF475F58E2 |
| root      | 127.0.0.1 | *6AF39F4D59F4D5A6B14A95DEE6242DBF475F58E2 |
| root      | ::1       | *6AF39F4D59F4D5A6B14A95DEE6242DBF475F58E2 |
| admin     | localhost | *0AF361ED82A69F4D5FCE6612ACD128DF5037EE64 |
| wordpress | localhost | *BA7AF377AB318426F499F4D59C26DFA937B15B72 |
+-----------+-----------+-------------------------------------------+
5 rows in set (0.000 sec)

MariaDB [mysql]>

Followed by:

GRANT ALL ON wordpress.* TO 'wordpress'@'localhost' IDENTIFIED BY ''xxxxxxxxx';

2 Detailed examples

/usr/libexec/mysqld –help –verbose

Using mysql is very easy. Invoke it from the prompt of your command interpreter as follows:

mysql db_name Or:

mysql --user=user_name --password=your_password db_name mysql -u root -p Login to mysql shell as root, and ask for a password prompt.

Then type an SQL statement, end it with “;”, \g, or \G and press Enter.

Typing Control-C causes mysql to attempt to kill the current statement. If this cannot be done, or Control-C is typed again before the statement is killed, mysql exits.

You can execute SQL statements in a script file (batch file) like this:

mysql db_name < script.sql > output.tab From MariaDB 10.4.6, mariadb is available as a symlink to mysql.

3 Resetting root password on first install

mysql -u root You should not have been asked for a root password. USE mysql set new password: mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpasswdhere'; mysql> FLUSH PRIVILEGES;

or if that did not work, try modifying the user table directly: mysql> UPDATE mysql.user SET authentication_string = PASSWORD('newpasswdhere'); if you said USE mysql that becomes: mysql> UPDATE user SET authentication_string = PASSWORD('xxxxxxxx') mysql> WHERE User = 'root' AND Host = 'localhost'; mysql> FLUSH PRIVILEGES;

You can also split up this command over multiple lines by delaying the semicolon; mysql> UPDATE user mysql> SET authentication_string = PASSWORD('xxxxxxxx') mysql> WHERE User = 'root' mysql> AND Host = 'localhost'; mysql> FLUSH PRIVILEGES;

UPDATE user SET authentication_string = PASSWORD('xxxxxxxx') WHERE User = 'root' =AND Host = 'localhost'; FLUSH PRIVILEGES;:q!

>>> Do NOT do the following unless you are running an older MySQL (<5.7) >>> mysql> update user >>> mysql> set password=PASSWORD('xxxxxxxx') >>> mysql> where User = 'root'; >>> >>> mysql> FLUSH PRIVILEGES; >>> mysql> >>> mysql> exit;

Whenever you change anything about MySQL permissions or user privileges you need to “flush” out the old ones, or essentially clear the cache and you do that with the simple command MySQL > flush privileges;

3.1 Recommendation to delete 2 of the 3 root accounts

mysql> select host, user, password from mysql.user; user and see:

MariaDB [mysql]> SELECT user, host, 
 -> password
 -> FROM user;
 +-----------+-----------+-------------------------------------------+
 | User      | Host      | Password                                  |
 +-----------+-----------+-------------------------------------------+
 | root      | localhost | *EACB2E99778C4B927C5E83E3C7E60347A0093FE6 |
 | root      | 127.0.0.1 | *EACB2E99778C4B927C5E83E3C7E60347A0093FE6 |
 | root      | ::1       | *EACB2E99778C4B927C5E83E3C7E60347A0093FE6 |
 | roundcube | localhost | *F72DA04145BB0B9442240279442D2983F41BC34D |
 +-----------+-----------+-------------------------------------------+
 4 rows in set (0.004 sec)

Three root users are generated by default when you first create a database. The are all created without passwords. At that time, the installation also recommends you set a password by running mysqladmin -u root password, which will change the password for 'root'@'%'.

The two that still don't have passwords are local to the host specified in the Host column; in both of these cases, it's the actual DB server. They do both have full access to the database by default. One might correctly surmise that, in many cases, if you were able to log in to the machine (which would be necessary to use either of these root accounts) in the first place, then you probably have access to the actual files on disk. So having passwords on these might not yield any real security.

That being said, I prefer to remove these default accounts, and stick with password-ed accounts.

DELETE FROM mysql.user WHERE Password=''; FLUSH PRIVILEGES

3.2 My vm1 output:

As of March 17th, 2020 MariaDB [(none)]> select user, password from mysql.user;

~~-----------~~-——————————————+

User

Password

~~-----------~~-——————————————+

root	*EACB2E99778C4B927C5E83E3C7E60347A0093FE6
root	*EACB2E99778C4B927C5E83E3C7E60347A0093FE6
root	*EACB2E99778C4B927C5E83E3C7E60347A0093FE6
roundcube	*F72DA04145BB0B9442240279442D2983F41BC34D

~~-----------~~-——————————————+ 4 rows in set (0.002 sec)

MariaDB [(none)]>

4 Connecting to remote MySQL server

How to Specify Which Protocol to Use When Connecting to the mysqld Server The following is true for all MySQL and MariaDB command line clients:

You can force which protocol to be used to connect to the mysqld server by giving the protocol option one of the following values: tcp, socket, pipe or memory.

If protocol is not specified, then the following happens:

Linux/Unix If hostname is not specified or hostname is localhost, then Unix sockets are used. Unused connection parameters (such as port) will be ignored. In other cases (hostname is given and it's not localhost) then a tcpip connection through the port option is used.

Note that localhost is a special value. Using 127.0.0.1 is not the same thing. The latter will connect to the mysqld server through tcpip.

5 MySQL sample databases

I found this sample databases in ntu.edu.sg (singapore) I found these samples in mysql.org: employees database sakila database

6 MariaDB 10.3 database server

Mariadb is an opensource database, that is a clone of MySQL. It was created as a fork from MySQL after Oracle bought MySQL, just to keep it opensourced. Oracle has been keeping MySQL open, but they could close it (start charging a fee) at any time, so it is good that mariadb is there step for step, to keep Oracle's MySQL opensource too.

Everything should work the same whether you install mariadb or mysql.

It appears that mariadb and mysqld are the same thing . see VM settings.org file for details.

6.1 man

man mysqld(8) for documentation (or online at mysql.org ?) mariadb.org or also: https://mariadb.com/kb/en/library/systemd/

systemctl (systemd) control over mariadb

7 resetting mysql root password

Stop mysql with: sudo systemctl stop mysql
Start wihout loading grant tables: sudo mysqld_safe --skip-grant-tables &
Login to mysql shell as root: mysql -u root You should not have been asked for a root password.
USE mysql
set new password: mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpasswdhere'; mysql> FLUSH PRIVILEGES;
or if that did not work, try modifying the user table directly: mysql> UPDATE mysql.user SET authentication_string = PASSWORD('newpasswdhere') if you said USE mysql that becomes: mysql> UPDATE user SET authentication_string = PASSWORD('g0jupamezu') mysql> WHERE User = 'root' AND Host = 'localhost';

Do not do the following unless you are running an older MySQL (<5.7) mysql> update user set password=PASSWORD('g0jupamezu') mysql> where User = 'root';

mysql> FLUSH PRIVILEGES; mysql> mysql> exit;
should see output like this: Query OK, 0 rows affected (0.00 sec)
Stop and Start the database normally: mysqladmin -u root -p shutdown sudo kill `/var/run/mariadb/mariadb.pid` sudo systemctl enable mariadb sudo systemctl start mariadb
Verify new password mysql -u root -p

7.1 better approach

On April 21, 2021 I had a half-installed mariadb. I tried resetting my root password, and ending up just using:

7.1.1 Then set muy root password (remembering to record it this time)

/usr/bin/mysql_secure_installation /usr/bin/mysql_secure_installation /usr/bin/mysql_secure_installation

DO THIS TO SET ROOT PASSWORD

7.2 Because the above did not work

Everything up to and including step 7 worked, however I could still not login to mysql using the new root password. SO, I re-installed mariadb as follows:

sudo systemctl stop mysql sudo systemctl disable mysql sudo dnf remove mysql

mysql # to test that it was uninstalled.

sudo dnf clean all sudo dnf install mariadb-server ( this was also still 10.3 ) sudo systemctl enable mariadb sudo systemctl start mariadb sudo systemctl status mariadb

tried to reset the root password using sudo /usr/bin/mysql_secure_installation

was not working… so will re-install a 2nd time !!!!!!!!!!!!!!!!!!! This time I also removed everthing in /etc/my.cnf.d and my.cnf

Ok, that did it. I had to manually set the root password first, but then it worked. Here's my session log of the second part (after the manual reset as described above):

mysql_secure_installation is used to set the default password, and permissions to make the installation secure (or at least more secure)

zintis@vm1 ~ $
sudo /usr/bin/mysql_secure_installation
sudo /usr/bin/mysql_secure_installation
sudo /usr/bin/mysql_secure_installation
sudo /usr/bin/mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] Y
New password: 
Re-enter new password: 
Sorry, passwords do not match.

New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB 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? [Y/n] Y
... Success!

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? [Y/n] Y
... Success!

By default, MariaDB 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? [Y/n] Y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] Y
... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
zintis@vm1 ~ $

See howtoforge.com for an example that mirrors my demo above.

8 Error on fresh install of mariadb: (still 10.3)


/usr/bin/mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):  < I just pressed hammer here >
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Enter current password for root (enter for none):

8.0.1 Then set muy root password (remembering to record it this time)

/usr/bin/mysql_secure_installation /usr/bin/mysql_secure_installation /usr/bin/mysql_secure_installation

DO THIS TO SET ROOT PASSWORD

9 mariadb primer

start here

zintis@vm1 ~ $
=which mysql_secure_installation=
the run it:
=/usr/bin/mysql_secure_installation=
zintis@vm1 ~ $
=mysql_secure_installation=

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
Enter current password for root (enter for none): 

Set root password? [Y/n] Y
New password: 
Re-enter new password: 
Sorry, passwords do not match.

New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB 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? [Y/n] Y
... Success!

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? [Y/n] Y
... Success!

By default, MariaDB 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? [Y/n] Y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.


Reload privilege tables now? [Y/n] Y
... Success!

01234  Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
zintis@vm1 ~ $

=============================================================================

10 Examples

use mysql; select * from user; select user, password from mysql.user;

11 Copied from VM-settings.org file

Adding php Feb 12 sudo dnf install php

11.0.1 Adding php-mysqlnd Feb 12

It turns out that php-mysql was not available. apparently php-mysqlnd, for "native drivers" is a direct replacement so I added that: sudo dnf install php-mysqlnd. php-mysqlnd is called the "MySQL native driver for PHP"

11.0.2 Adding mariadb and mariadb-server Feb 12

If the mysqlnd does not work out, an alternative is mariadb. I installed it: sudo dnf install mariadb sudo dnf install mariadb-server

11.1 Installed but not enabled (yet)

I decided to try php-mysqlnd first, and leave mariadb installed, but not enabled. So, I only enabled (and started) php-mysqlnd.

Actually I tried systemctl start mysqld and it appears to been linked to mariadb so this command:

sudo systemctl start mysqld sudo systemctl status mysqld

started MariaDB 10.3 database server.

   sudo systemctl status mysqld
● mariadb.service - MariaDB 10.3 database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2020-02-11 23:47:33 EST; 37min ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
 Main PID: 16368 (mysqld)
   Status: "Taking your SQL requests now..."
    Tasks: 30 (limit: 5048)
   Memory: 87.6M
   CGroup: /system.slice/mariadb.service
           └─16368 /usr/libexec/mysqld --basedir=/usr

Feb 11 23:47:33 vm1.localdomain mysql-prepare-db-dir[16265]: Please report any problems at http://mariadb.org/jira
Feb 11 23:47:33 vm1.localdomain mysql-prepare-db-dir[16265]: The latest information about MariaDB is available at http://mariadb.org/.
Feb 11 23:47:33 vm1.localdomain mysql-prepare-db-dir[16265]: You can find additional information about the MySQL part at:
Feb 11 23:47:33 vm1.localdomain mysql-prepare-db-dir[16265]: http://dev.mysql.com
Feb 11 23:47:33 vm1.localdomain mysql-prepare-db-dir[16265]: Consider joining MariaDB's strong and vibrant community:
Feb 11 23:47:33 vm1.localdomain mysql-prepare-db-dir[16265]: https://mariadb.org/get-involved/
Feb 11 23:47:33 vm1.localdomain mysqld[16368]: 2020-02-11 23:47:33 0 [Note] /usr/libexec/mysqld (mysqld 10.3.17-MariaDB) starting as process 163>
Feb 11 23:47:33 vm1.localdomain mysqld[16368]: 2020-02-11 23:47:33 0 [Warning] Could not increase number of max_open_files to more than 1024 (re>
Feb 11 23:47:33 vm1.localdomain mysqld[16368]: 2020-02-11 23:47:33 0 [Warning] Changed limits: max_open_files: 1024  max_connections: 151 (was 1>
Feb 11 23:47:33 vm1.localdomain systemd[1]: Started MariaDB 10.3 database server.

sudo systemctl start mariadb-server did not work but ... mariadb did work.

So for good measure, I also started (and enabled) mariadb: sudo systemctl start mariadb and shortly after sudo systemctl enable mariadb

12 From support.rackspace.com

https://support.rackspace.com/how-to/configure-mariadb-server-on-centos/

Find the config files By default, you’ll find MariaDB’s configuration file at the following location:

/etc/my.cnf If it’s not there you can use mysqld to look for the configuration file by running the following command:

/usr/libexec/mysqld --help --verbose

You’ll get a lot of text back. The first part describes the options that you can send to the server when you launch it. The second part is all of the configuration information that was set when the server was compiled.

Near the start of the output, find a couple lines that look similar to the following lines:

Default options are read from the following files in the given order: etc/mysql/my.cnf /etc/my.cnf ~.my.cnf /etc/my.cnf.d/my.cnf~ The server works down the list until it finds a configuration file.

my.cnf Open the my.cnf file and have a look inside.

Any lines starting with # are comments, and they mostly document what the different settings are for. You’ll find details like the location of log files and where the database files are kept.

Config groups There are lines in the config file that just contain a word in square brackets like [client] or [mysqld]. Those are config groups and they tell the programs that read the configuration file which parts they should pay attention to.

MariaDB is technically a collection of tools that includes the server (mysqld), the client (mysql), and some other tools. Programs look in my.cnf to see how they should behave.

Basically, the mysql configuration section controls the client, and the mysqld section controls the server.

13 Log files

If something goes wrong, start troubleshooting by checking mariadb's logs. By default MariaDB stores its log files in the following directory: /var/log/mariadb If they are not there, look in my.cnf for "log_error = /var/log/mariadb/mariadb.log"

13.1 /var/log/mariadb

Note: You might need to use sudo to get a listing of the files in that directory.

If you don’t find the logs in the default directory, you need to check MariaDB’s configuration. Look in the my.cnf file and look for a log_error line, as in:

lst log_error = /var/log/mariadb/mariadb.log If you don’t see a line like that, create one in the mysqld section so that MariaDB will use its own error log. We recommend using the location in the example and creating the /var/log/mariadb directory if it doesn’t already exist. Apply the change by restarting MariaDB with the following command:

systemctl restart mariadb Make sure that the log directory you choose can be written to by the user controlling the MariaDB process.

======================

14 Sample mysql queries I can make:

<?php $mysqli = new mysqli("localhost", "root", "<password>");

if ($mysqli->connect_errno) { echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error; } echo $mysqli->host_info . "\n"; ?>

14.1 MySQL commands directly from the shell:

You can actually perform any command from your regular BASH command prompt.

echo SET PASSWORD FOR ‘anthony’@’localhost’ = PASSWORD(‘text password’); | mysql

echo “command here” | mysql will “pipe” the command inside of mysql to be executed and the results will output via standard output to your BASH shell!

14.2 List what tables exist so far:

You can run the following query to check for the existance of the user table.

SELECT * FROM information_schema.TABLES WHERE TABLE_NAME LIKE '%user%' See if you can find a row with the following values in

14.3 List databases

mysql> show databases;

~~--------------------~~

Database

~~--------------------~~

information_schema

datapass_schema

mysql

~~--------------------~~ 4 rows in set (0.05 sec)

mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Database changed mysql> show tables; -> ;

~~---------------------------~~

Tables_in_mysql

~~---------------------------~~

column_stats

des | columns_priv |

event

func

general_log

global_priv

gtid_slave_pos

help_category

help_keyword

help_relation

help_topic

index_stats

innodb_index_stats

innodb_table_stats

plugin

proc

procs_priv

proxies_priv

roles_mapping

servers

slow_log

table_stats

tables_priv

time_zone

time_zone_leap_second

time_zone_name

time_zone_transition

time_zone_transition_type

transaction_registry

user

~~---------------------------~~ 31 rows in set (0.001 sec)

MariaDB [mysql]>

14.4 checking and fixing user databse, including root.

mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Database changed mysql> update user set password=PASSWORD("password") where User='root'; ERROR 1054 (42S22): Unknown column 'password' in 'field list' mysql> desc user;

14.5 For debian systems :

Useful advice on checking (and changing) root password:

you can try to reconfigure the mysql-server : sudo dpkg-reconfigure mysql-server
check if you have the debian-sys-maint passwd
cat /etc/mysql/debian.cnf and check for :

user = debian-sys-maint password = xxxxGx0fSQxxGa debian-sys-maint has all privileges on the mysql server
if it do not solve the problem, you can reset the passwd :
sudo service mysql stop * On a debian system * sudo mysqld_safe --skip-grant-tables & * on a debian syst3em* mysql -u root -p

mysql> use mysql; mysql> update user set password=PASSWORD("NewPasswd") where User='root'; mysql> flush privileges; mysql> quit

sudo systemctl stop mysql sudo systemctl start mysql mysql -u root -p

=============================== I ran into a couple issues with the accepted answer.

First time I ran sudo service mysql stop it didn't stop the service and I didn't realize it. Do a ps -fe | grep mysql. So make sure the process is done before running any of the following commands. After some tries I ended up killing it with sudo kill -KILL <mysqld process id>.

Then after running the sql update, again service mysql stop didn't work. So check your process id with ps -fe | grep mysqld_safe and use sudo kill -TERM <process id> as instructed below. If you have more than one process use the one with the lowest id.

sudo service mysql stop sudo mysqld_safe –skip-grant-tables & mysql -u root

mysql> use mysql; mysql> update user set password=PASSWORD("NewPasswd"), plugin='mysql_native_password', host='%' where User='root'; mysql> flush privileges; mysql> quit

sudo kill -TERM <mysqld_safe process id> sudo service mysql start mysql -u root -p =========================== This helped me, thanks. Only in my case I used authentication_string instead of password, that is UPDATE user set authentication_string = PASSWORD('101010'), plugin = 'mysql_native_password', where User = 'root' and Host = 'localhost'; I have MySQL 5.7 installed. And I killed processes with sudo killall mysql and sudo killall mysqld

========================== I had to do this on CentOS 8:

sudo systemctl stop mysqld sudo /usr/sbin/mysqld –skip-grant-tables –skip-networking –user root mysql -u root

use mysql; FLUSH PRIVILEGES; ALTER USER 'root'@'localhost' IDENTIFIED BY 'My^Pass1'; exit

sudo pkill mysqld sudo systemctl start mysqld mysql -u root -p

========================== I've installed MySQL server on a remote Ubuntu machine. The root user is defined in the mysql.user table this way:

mysql> SELECT host, user, password FROM user WHERE user = 'root';

host	user	password
localhost ip-10-48-110-188 127.0.0.1 ::1	root root root root	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

I can access with user root from the same remote machine command-line interface using the standard mysql client. Now I want to allow root access from every host on the internet, so I tried adding following row (it's an exact duplicate of the first row from previous dump, except for the host column):

mysql> SELECT host, user, password FROM user WHERE host = '%';

host	user	password
%	root	*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

But my client on my personal PC continues to tell me (I obscured the server IP):

SQL Error (2003): Can't connect to MySQL server on '46.x.x.x' (10061)

I can't tell if it's a authentication error or a network error. On the server firewall I enabled port 3306/TCP for 0.0.0.0/0, and that's ok for me…

341

There's two steps in that process:

a) Grant privileges. As root user execute with this substituting 'password' with your current root password : GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password'; b) bind to all addresses: The easiest way is to comment out the line in your my.cnf file: #bind-address = 127.0.0.1 and restart mysql

service mysql restart By default it binds only to localhost, but if you comment the line it binds to all interfaces it finds. Commenting out the line is equivalent to bind-address=*.

To check where mysql service has binded execute as root:

netstat -tupan | grep mysql Update For Ubuntu 16:

=============================== In order to connect remotely you have to have MySQL bind port 3306 to your machine's IP address in my.cnf. Then you have to have created the user in both localhost and '%' wildcard and grant permissions on all DB's as such . See below:

my.cnf (my.ini on windows)

#Replace xxx with your IP Address bind-address = xxx.xxx.xxx.xxx then

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass'; CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass'; Then

GRANT ALL ON . TO 'myuser'@'localhost'; GRANT ALL ON . TO 'myuser'@'%'; GRANT ALL PRIVILEGES ON database.table TO 'user'@'localhost'; flush privileges; Depending on your OS you may have to open port 3306 to allow remote connections.

==========================================

for what DB is the user? look at this example

mysql> create database databasename; Query OK, 1 row affected (0.00 sec) mysql> grant all on databasename.* to cmsuser@localhost identified by 'password'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) so to return to you question the "%" operator means all computers in your network.

like aspesa shows I'm also sure that you have to create or update a user. look for all your mysql users:

SELECT user,password,host FROM user; as soon as you got your user set up you should be able to connect like this:

mysql -h localhost -u gmeier -p hope it helps

==========================================

computing for geeks.com has a nice overview on installing MariaDB 10.4 on CentOS8 However, I had already upgraded to 10.4 so I did not follow these instructions.

==========================================

15 Upgrade from 10.3 to 10.4

I followed the instructions on mariadb.com

Basicaclly,

Modify the repository configuration, so the system's package manager installs MariaDB 10.4.
I configured yum to install from MariaDB Corporation's MariaDB Package Repository by using the MariaDB Package Repository setup script, From here: mariadb.com
stop mariadb
sudo dnf remove mariadb
sudo dnf install MariaDB-server ~~galera-4 MariaDB-client MariaDB-shared~~ MariaDB-backup MariaDB-common Note: As of Mar 15th, 2020, I only installed MariaDB-server galera-4 is for MySQL clusters (good for production, not tested here) MariaDB-client # not yet installed MariaDB-shared # not installed contains dynamic client libraries MariaDB-backup # not installed MariaDB-common # not yet installed but has my.cnf and character set files See this mariadb.com link for details on each of these components.
Make any desired changes to configuration options in option files, such as my.cnf (if you installed MariaDB-shared
systemctl start mariadb
run mysql_upgrade which fixes and cleans up user config database

15.1 Read 10.4 release notes

From mariadb.com

15.2 After upgrading:

root@vm1 ~ [650]$ mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 46 Server version: 10.4.12-MariaDB MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;

~~--------------------~~

Database

~~--------------------~~

information_schema

mysql

performance_schema

~~--------------------~~ 3 rows in set (0.012 sec)

MariaDB [(none)]>

16 Problem and fix after upgrading from 10.3 to 10.4

16.1 Error on trying to secure mysql

After intalling, the first step one should do is secure your mysql install. Do this by running mysql_secure_installation as root.

I was getting this error, half-way through:

Remove anonymous users? [Y/n] Y
ERROR 1146 (42S02) at line 1: Table 'mysql.global_priv' doesn't exist
... Failed!
Cleaning up...
root@vm1 ~ [642]$

16.2 Fix 1 (failed)

I ran :

root@vm1 ~ [642]$ 
mysql_install_db
mysql.user table already exists!
Run mysql_upgrade, not mysql_install_db
root@vm1 ~ [643]$ 
mysql_upgrade
Version check failed. Got the following error when calling the 'mysql' command line client
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
FATAL ERROR: Upgrade failed

16.3 Fix 2 (success)

So, mysql_upgrade apparently did not work. It does work, you just need to run it with a proper password; mysql_upgrade -p

.
.
.
mysql.time_zone_transition_type                    OK
mysql.transaction_registry                         OK
mysql.user                                         OK
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
Phase 4/7: Running 'mysql_fix_privilege_tables'
Phase 5/7: Fixing table and database names
Phase 6/7: Checking and upgrading tables
Processing databases
information_schema
performance_schema
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK

16.4 Rerun `mysql_secure_installation` with success.

This time I had success, with running mysql_secure_installation as root.

My mariadb upgrade with MariaDB 10.4 was up and ready for use.

17 MySQL commands

There are also a number of commands that can be run inside the client. Note that all text commands must be first on line and end with ';'

Command Description ?, \? Synonym for `help'. clear, \c Clear the current input statement. connect, \r Reconnect to the server. Optional arguments are db and host. delimiter, \d Set statement delimiter. edit, \e Edit command with $EDITOR. ego, \G Send command to mysql server, display result vertically. exit, \q Exit mysql. Same as quit. go, \g Send command to mysql server. help, \h Display this help. nopager, \n Disable pager, print to stdout. notee, \t Don't write into outfile. pager, \P Set PAGER [to_pager]. Print the query results via PAGER. print, \p Print current command. prompt, \R Change your mysql prompt. See prompt command for options. quit, \q Quit mysql. rehash, \# Rebuild completion hash. source, \. Execute an SQL script file. Takes a file name as an argument. status, \s Get status information from the server. system, \! Execute a system shell command. Only works in Unix-like systems. tee, \T Set outfile [to_outfile]. Append everything into given outfile. use, \u Use another database. Takes database name as argument. charset, \C Switch to another charset. Might be needed for processing binlog with multi-byte charsets. warnings, \W Show warnings after every statement. nowarning, \w Don't show warnings after every statement.

17.1 view users on your database

To view the users defined on your database, you can query mysql.user table, e.g.

SELECT user, host FROM mysql.user;

select * from mysql.user; # will give you too much. select user, password from mysql.user; # much better what fields are avaiable in a table?? Fields are called columns so better to ask: what columns are available in a table??

17.2 To output all columns from table(s)

SHOW COLUMNS FROM table-name

SELECT * FROM information_{schema.columns} WHERE table_schema = 'your_db' ORDER BY table_name,ordinal_position;

SELECT COLUMN_NAME FROM information_{schema.columns} WHERE table_schema = 'your_db' ORDER BY table_name,ordinal_position;

SELECT * FROM information_{schema.columns} WHERE table_schema = 'your_db' # could also try WHERE table_schema = DATABASE() ORDER BY table_name,ordinal_position;

To look just at one table. SELECT * FROM information_{schema.columns} WHERE table_schema = 'your_db' AND table_name = 'your_table_name';

To just get column names: SELECT column_name FROM information_{schema.columns} WHERE table_schema='your_db' ORDER BY table_name, ordinal_position

Even better: SHOW columns FROM tablename

17.3 If you want a comma delimited list of your columns

On the offchance that it's useful to anyone else, this will give you a comma-delimited list of the columns in each table:

SELECT table_name,GROUP_CONCAT(column_name ORDER BY ordinal_position) FROM information_{schema.columns} WHERE table_schema = DATABASE() GROUP BY table_name ORDER BY table_name ;

17.4 Putting that into php:

# this is not bash but php.
   <?php
       $table = 'orders';
       $query = "SHOW COLUMNS FROM $table";
       if($output = mysql_query($query)):   # mysql_query has been depracted by mysqli
           $columns = array();
           while($result = mysql_fetch_assoc($output)):
               $columns[] = $result['Field'];
           endwhile;
       endif;
       echo '<pre>';
       print_r($columns);
       echo '</pre>';
  ?>

Another apprpoach for some readable php:

# this is not bash, but php code.
  $a = mysqli_query($conn,"select * from information_schema.columns
     where table_schema = 'your_db'
     order by table_name,ordinal_position");
  $b = mysqli_fetch_all($a,MYSQLI_ASSOC);
  $d = array();
  foreach($b as $c){
     if(!is_array($d[$c['TABLE_NAME']])){
         $d[$c['TABLE_NAME']] = array();
     }
     $d[$c['TABLE_NAME']][] = $c['COLUMN_NAME'];
  }

  echo "<pre>",print_r($d),"</pre>";

18 More MySQL cookbook commands

18.1 Grant user privileges

MySQL > =create database newdb;=
MySQL > =GRANT USAGE ON newdb.* TO ‘anthony’@’localhost’=
MySQL > =IDENTIFIED BY ‘text password’;=

or just
MySQL > =GRANT USAGE ON newdb.* TO ‘anthony’@’localhost’;=
-------------------------------------
mysql > =GRANT  ALL PRIVILEGES ON roundcubemail.*=
mysql > =TO 'roundcube'@'localhost';=

18.2 To another remote host

Could also be to a remote host in which case it would be mysql > TO 'roundcube'@'%'; i.e. replace localhost with % for any host

Rather than providing all privileges to the entire database, perhaps you want to give the tolkien user only the ability to read data (SELECT) from the authors table of the books database. You could accomplish that with:

mysql> GRANT ALL PRIVILEGES ON books.authors TO 'tolkien'@'localhost';

18.3 To show Grants for a user

mysql > SHOW GRANTS FOR 'user'@'localhost';

18.4 To create a user that can connect from any host:

mysql > CREATE USER 'newuser'@'%' IDENTIFIED BY 'user_password';

18.5 To create a user that can connect from 5.5.5.5 host:

mysql > CREATE USER 'newuser'@'5.5.5.5' IDENTIFIED BY 'user_password'

18.6 delete a specific user

DELETE FROM mysql.user WHERE user = 'username';

18.7 Common privileges

The most commonly used privileges are:

ALL PRIVILEGES – Grants all privileges to a user account. CREATE – The user account is allowed to create databases and tables. DROP - The user account is allowed to drop databases and tables. DELETE - The user account is allowed to delete rows from a specific table. INSERT - The user account is allowed to insert rows into a specific table. SELECT – The user account is allowed to read a database. UPDATE - The user account is allowed to update table rows. To grant specific privileges to a user account, you can use the following syntax:

GRANT permission1, permission2 ON database_name.table

18.8 Display a user's privileges

SHOW GRANTS FOR 'database_user'@'localhost';

~~---------------------------------------------------------------------------~~

Grants for database_user@localhost

~~---------------------------------------------------------------------------~~

GRANT USAGE ON . TO 'database_user'@'localhost'

GRANT ALL PRIVILEGES ON `database_name`.* TO 'database_user'@'localhost'

~~---------------------------------------------------------------------------~~ 2 rows in set (0.00 sec)

18.9 Revoke a user's privilege

mysql > REVOKE ALL PRIVILEGES ON roundcubemail.* FROM 'fired-user'@'localhost';

18.10 Remove a user altogether

mysql > DROP USER 'user'@'localhost'

My cheatsheet on mysql

1 MySQL cheat commands

1.1 Ummmm, what is going on at zintis.net

2 Detailed examples

3 Resetting root password on first install

3.1 Recommendation to delete 2 of the 3 root accounts

3.2 My vm1 output:

4 Connecting to remote MySQL server

5 MySQL sample databases

6 MariaDB 10.3 database server

6.1 man

7 resetting mysql root password

7.1 better approach

7.1.1 Then set muy root password (remembering to record it this time)

7.2 Because the above did not work

8 Error on fresh install of mariadb: (still 10.3)

8.0.1 Then set muy root password (remembering to record it this time)

9 mariadb primer

10 Examples

11 Copied from VM-settings.org file

11.0.1 Adding php-mysqlnd Feb 12

11.0.2 Adding mariadb and mariadb-server Feb 12

11.1 Installed but not enabled (yet)

12 From support.rackspace.com

13 Log files

13.1 /var/log/mariadb

14 Sample mysql queries I can make:

14.1 MySQL commands directly from the shell:

14.2 List what tables exist so far:

14.3 List databases

14.4 checking and fixing user databse, including root.

14.5 For debian systems :

15 Upgrade from 10.3 to 10.4

15.1 Read 10.4 release notes

15.2 After upgrading:

16 Problem and fix after upgrading from 10.3 to 10.4

16.1 Error on trying to secure mysql

16.2 Fix 1 (failed)

16.3 Fix 2 (success)

16.4 Rerun mysql_secure_installation with success.

17 MySQL commands

17.1 view users on your database

17.2 To output all columns from table(s)

17.3 If you want a comma delimited list of your columns

17.4 Putting that into php:

18 More MySQL cookbook commands

18.1 Grant user privileges

18.2 To another remote host

18.3 To show Grants for a user

18.4 To create a user that can connect from any host:

18.5 To create a user that can connect from 5.5.5.5 host:

18.6 delete a specific user

18.7 Common privileges

18.8 Display a user's privileges

18.9 Revoke a user's privilege

18.10 Remove a user altogether

18.11 Home

16.4 Rerun `mysql_secure_installation` with success.