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
  • 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;
• ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
• FLUSH PRIVILEGES;
• SELECT user,authentication_string,plugin,host FROM mysql.user;
• Digital Ocean Guide