mysql mariadb

Si queremos que nuestro usuario root tenga contraseña

ubuntu 24 Entramos a mysql

sudo mysql

Creamos una contraseña

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mySecurePassword';
FLUSH PRIVILEGES;

Reiniciamos

sudo service mysql restart

Login

  $ mysql -u root -p

To connect, you just have to use whatever client you require, such as the basic mysql client.

  $ mysql -h localhost -u user database

Check version

  $ mysql --version
  Ubuntu 16.04.4 LTS -> mysql  Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using  EditLine wrapper
  Ubuntu 14.04.5 LTS -> mysql  Ver 14.14 Distrib 5.5.61, for debian-linux-gnu (x86_64) using readline 6.3

Test which port MySQL is running

$ netstat -tln
  Port 3306 is MySql's default port.

Check what port mysql is running on:

mysql> SHOW GLOBAL VARIABLES LIKE 'PORT';

File my.cnf

Debian Linux file is located at /etc/mysql/my.cnf location.
Red Hat Linux/Fedora/Centos Linux file is located at /etc/my.cnf location.
FreeBSD you need to create a file /var/db/mysql/my.cnf location.

Restart

In RHEL:

$ sudo systemctl restart mysqld
$ service mysql restart

Allow mysql remote access

First, find the address MySQL is bound to

$ cat /etc/mysql/my.cnf | grep bind-address

Or server ubuntu16

$ cat /etc/mysql/mysql.conf.d/mysqld.cnf
$ nano /etc/mysql/mysql.conf.d/mysqld.cnf

Now we you must find this line:

bind-address = 127.0.0.1

And replace with:

bind-address = 0.0.0.0

Also, make sure this line is commented (with a # in the beginning):

# skip-networking

Let’s run nmap again to conPORT STATE SERVICE

$ namp 192.168.4.4
  22/tcp   open  ssh
  80/tcp   open  http
  3306/tcp open  mysql
  firm if the port 3306 is opened in the public IP:

Create a new MySQL User Account

A user account in MySQL consists of two parts: user name and host name.

CREATE USER 'sstamericanair'@'localhost' IDENTIFIED BY 'user_password';

In the example above, the hostname part is set to localhost, which means that the user will be able to connect to the MySQL server only from the localhost (i.e. from the system where MySQL Server runs).

User's privileges in MySQL

=> GRANT ALL ON [database name].[table name] TO [user name]@[host name]; Samples

mysql> GRANT ALL on foo.* TO root@'192.168.33.1' IDENTIFIED BY 'Follow1ngcommand?.,!';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'eder'@'192.168.33.1' IDENTIFIED BY 'Follow1ngcommand?.,!' WITH GRANT OPTION;
mysql> GRANT ALL ON jobeet.* TO [email protected] IDENTIFIED BY '!root+123+abc+ABC!';

Grant access from all machines

  mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

Grant a user permission to only view a mysql view

  mysql> GRANT SELECT ON database1.view1 TO 'someuser'@'somehost';

Add a user that can connect from anywhere.

$ mysql -u root -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '' WITH GRANT OPTION; FLUSH PRIVILEGES;"

Revoke all grants for a mysql user

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM [email protected];

Remove/delete the user from the user table

mysql> DROP USER 'bloguser'@'localhost';

View a list of MySQL users and their privileges

mysql> SELECT user,host FROM mysql.user;

List grants for a mysql user

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

To find the privilege(s) granted to a particular MySQL account:

mysql> SHOW GRANTS FOR 'root'@'%';

If you change the IP of your router, you must update the IP on the mysql server

mysql> UPDATE mysql.user SET host = 'mynewip' WHERE user = 'sst';
mysql> GRANT ALL PRIVILEGES ON events.* TO 'sst'@'mynewip';

Then

service mysql restart

As above, when you specify a particular MySQL account to check for privileges, use an account name constructed by user name and host name concatenated by "@".

Change password for mysql user

mysql> set password for 'eder'@'192.168.33.11'=PASSWORD('anypassword);
  Query OK, 0 rows affected, 1 warning (0.00 sec)

View Log