IT/Software/Database Applications/mysql command line/mysql commands

Here we will list helpful things to do on mysql

When we say mysql that means you are on a server and are running mysql

• From the command line type mysql -u root
  • or whatever user you want
  • Enter your password
    • It will not appear
• Now you are in mysql!

Deleting data older than a date

• Get into mysql as a root or otherwise privileged user
  • You can also login in as the user to the database you want to work on.
  • But if that use has global or high privileges you stand the cnace of breaking something
    • Don't be fooled by the above statement. You ALWAYS stand the chance of breaking something.
• I use phpMyAdmin to make sure you know the right table names and column names.
• Select the database you want

use databasetoworkon

• Run the command
  • DELETE will be the command to perform.
  • FROM will select the table you will work on
  • WHERE is the condition
  • "timecreated" is the name of the column
    • NOTE that we got this from phpMyAdmin or any tool you like.

DELETE FROM mdl_files WHERE timecreated < 1580032365;

• You had better be sure since once you hit enter there is no going back.

Restoring root access

• For some reason root access by password was removed from the defaults at some point
  • I first noticed this on Ubuntu Server 18.04
• There are many and varied ways to restore this.
  • Beware not all of them work.
  • The below one was derived from a Tutorial on Digital Ocean
    • Thanks guys!

sudo mysql

• Next, check which authentication method each of your MySQL user accounts use with the following command:

SELECT user,authentication_string,plugin,host FROM mysql.user;

• Now alter the user with the following.
  • change the word password to whatever you want.
    • whatever you want except for any symbols that mysql may interpret as commands
      • Good luck finding a definitive and applicable list.
      • That is why all of my passwords are my birthday:)
        • But at my age and memory status that is risky.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

• Your spouse is probably looking for such a command to alter you!
• Now do the following.
  • I don't think you really need to anymore but why not.

FLUSH PRIVILEGES;

• Check again to see if what you did took.

SELECT user,authentication_string,plugin,host FROM mysql.user;

• Here is the Digital Ocean Guide