-
-
Notifications
You must be signed in to change notification settings - Fork 537
MySQL tips
This tips were prepared for Install Tracks 2.5 on Ubuntu 20.10
https://dev.mysql.com/doc/refman/8.0/en/
for Ubuntu or Debian distributions
To enable or disable the automatic start of MySQL service when you boot your machine use
tmp/partkeepr>systemctl enable mysql
>systemctl disable mysql
To start or stop manually the MySQL service use
>systemctl start mysql.service
>systemctl stop mysql.service
To show the status use
>systemctl status mysql.service
also check the errors with
>journalctl -xe
from the Linux terminal console type
>mysql -u root -p
or for a specific user
>mysql -u user_name -p
since Mysql version 8.0 the default values has been changed from mysql_native_password to caching_sha2_password
this needs to be reverted in the cnf file
for my installation /etc/mysql/my.cnf is a symbolic link to /etc/alternatives/my.cnf which is a symbolic link to /etc/mysql/mysql.cnf
so we need to edit the file /etc/mysql/mysql.cnf
add the following at the end of the file
[mysqld]
default_authentication_plugin=mysql_native_password
the group [mysqld] is for the server, for the client the group is [mysql]
(info href="http://dev.mysql.com/doc/mysql/en/server-system-variables.html
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html )
possible places of the cnf file
- /etc/my.cnf
- /etc/mysql/my.cnf
- $MYSQL_HOME/my.cnf
- [datadir]/my.cnf
- ~/.my.cnf
>mysqldump -u user_name -p db_name > /path/exported_data.sql
set user_name and db_name accordingly.
e.g.
>mysqldump -u daniel -p tempe > ~/tracks.sql
The command will ask for the password of the SQL user.
If you get the error mysqldump: Error: 'Access denied;
you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
if your user root doesn't have a password ... you can use root (note that the line is without -p)
>mysqldump -u root tempe > ~/tracks.sql
Sometimes it is required to drop all the table before the import, you need to check for your case.
at the command line do
>mysql -u user_name -p db_name < /path/exported_data.sql
e.g.
>mysql -u daniel -p tempe < ~/tracks.sql
These are examples on how to grant some privileges
mysql> GRANT USAGE ON *.* TO user_name @localhost;
mysql> GRANT ALL PRIVILEGES ON thisdb .* TO user_name @localhost;
mysql> GRANT GRANT OPTION ON thisdb .* TO user_name @localhost; # Enables you to grant to or revoke from other users those privileges that you yourself possess.
mysql>GRANT PROCESS ON *.* TO user_name @localhost;
mysql>quit;
Show privileges granted to the current MySQL user
mysql> SHOW GRANTS;
Show privileges granted to the MySQL user (if you don’t specify a host for the user name, MySQL assumes % as the host):
mysql> SHOW GRANTS FOR 'user_name';
Show privileges granted to a particular MySQL user account from a given host:
mysql> SHOW GRANTS FOR 'user_name'@'host';