Remote mysql logon backup in CentOS

Remote mysql logon backup in CentOS

1. Change the remote root password

Mysqladmin-h ip-uroot-p123456 password

If 'Access denied for user' root' @ 'IP' (using password: YES) 'appears )'

If the permission is insufficient, the remote host cannot log on except localhost.

In this case, check the root host permission.

We can see that the host can only log on to the local machine, but cannot log on to the remote host,

At this time, you need to modify the permission,

Show grants for 'root' @ '% ';

We can see that the root permission is not only on the local machine.

Then perform root remote login on another host

Then, change the remote host password.

Mysqladmin-h ip-uroot-p123456 password to change the remote root login password

In addition, the password for remote logon to the host is the remote root instead of the password of the remote host you want to log on to. The two are different.

I haven't figured it out at the beginning.

6. ps aux | grep mysqld

You can view the file running location and Data Location

7. delete user permissions in the database

Revoke all on database name. * from 'user' @ '%' identified by 'Password ';

8. Remote Host backup database

Mysqldump-h 192.168.121.13-uroot-pyzg1314520 test> test. SQL (you must have a test database)

Export Database to text backup

Mysqldump-uroot-pyzg1314520 -- databases zabbix> zabbix. test

Restore

Mysqldump-h 192.168.121.13-uroot-pyzg1314520 test <test. SQL

9. Grant User Permissions

Grant all on user. * to 'user' @ '%' identified by 'Password ';

10. Enter information_schema to view the size of all databases.

Use information_schema;

Select concat (round (sum (data_length/1024/1024), 2), 'mb') as data from tables;

11. view the specified database size

Use information_schema;

Select concat (round (sum (data_length/1024/1024), 2), 'mb') as data from tables where table_schema = 'database name ';

12. view the size of a table in the specified database

Use information_schema;

Select concat (round (sum (data_length/1024/1024), 2), 'mb') as data from tables where table_schema = 'Home' and table_name = 'table name ';