MYSQL
Well this is a little more advanced but is a very useful skill to have. Once you are able to ssh into servers you will have user access to work with MYSQL. I will start with some basic commands that you will use on a daily basis. If a customer contacts and wants use to import database for them we can do that. Sometimes it is a super easy and sometimes it is a pain in the ass. If the sql file is corrupt the customer will need to try and upload a new one.
Example of how to import a database:
mysql -p -u user_name database_name < file.sql
The customer will of needed to upload the database into a directory so that we can get to it. One you run this command it will prompt for a password as well. The customer will need to provide the file information:
Location of sql file: home/saltyl3
Name of sql file: saltyl3_wordp1.sql
Database username: saltyl3_wp
Database name: saltyl3_wp
Database password: moresaltplease
If the customer has already uploaded the config file with that info you can use this command to grep it real quick.
grep ‘DB_[UNP]’ ./wp-config.php
Then it would like look something like this: mysql -p -u saltyl3_wp saltyl3_wp < saltyl3_wordp1.sql
Once you run that it will ask for the password. If you do not get an error then you good. If you do get an error that the credentials are wrong CHECK and make sure that the password is correct if not reset it and update it in the wp_config.php. You can always verify if it is correct by trying to access that MYSQL database by running the following command:
mysql -u saltyl3_wp -p saltyl3_wp
If the password works then its all good.
- Common MYSQL Commands:
CREATE TABLE creates a new table.
- INSERT INTO adds a new row to a table.
- SELECT queries data from a table.
- UPDATE edits a row in a table.
- ALTER TABLE changes an existing table.
- DELETE FROM deletes rows from a table
Show the current enabled theme
mysql> SELECT option_name,option_value FROM wp_options WHERE option_id=45;
Change theme back to twentyeleven (default theme)
mysql> UPDATE wp_options SET option_value = ‘twentyeleven’ WHERE option_name = ‘template’ OR option_name = ‘stylesheet’;
Change the WordPress siteurl and homeurl:
mysql> UPDATE wp_options SET option_value = ‘http://newurlgoeshere.tld/’ WHERE option_name = ‘siteurl’ OR option_name = ‘home’;
How to change a wordpress user password:
SELECT ID, user_login, user_pass FROM wp_users; – First check the user to find out what the id is.
update wp_users set user_pass=md5(‘mynewpassword’) where ID=1; – Change the password
FLUSH PRIVILEGES; – Make sure to flush after lol
How to create a user:
CREATE USER ‘sith’@’localhost’ IDENTIFIED BY ‘lord’;
(Sith is the user and lord is the password)
How to grant all privileges to a user:
GRANT ALL PRIVILEGES ON practice.* TO ‘sith’@’localhost’;
How to list all user:
SELECT User FROM mysql.user;
How to show privileges of a user:
show grants for ‘sith’@’localhost’;
How to change the password of a myql user:
SET PASSWORD FOR ‘sith’@’localhost’ = PASSWORD (‘lordvadar’);
More MYSQL
Creating Tables;
SELECT * FROM example_table – Select a certain table from a Database. (The wild card * allows you to select every column in a table without you having to name each one.
CREATE TABLE lotr (id INTEGER, name TEXT, age INTEGER); – Creating a Table
INSERT INTO lotr (id, name, age) VALUES (1, ‘Gandalf The Grey’, 2019); – Adds a row to the table.
SELECT * FROM lotr; – view the table that was just created above.
INSERT INTO celebs (id, name, age) VALUES (2, ‘Aragorn Strider’, 87);
INSERT INTO celebs (id, name, age) VALUES (3, ‘Gimli’, 139); – add 3 more Lord of the Rings Characters to the table;
INSERT INTO celebs (id, name, age) VALUES (4, ‘Samwise Gamshee’, 61);
SELECT name FROM lotr; – – view the tables that was just created above.
UPDATE lotr
SET age = 22
WHERE id = 1; – Editing a row in the table
SELECT * FROM celebs; – Checking the row after editing.
ALTER TABLE lotr ADD COLUMN middle_earth TEXT; – Add a new column to the table;
SELECT * FROM lotr; – – Checking the row after editing
UPDATE lotr
SET twitter_handle = ‘@gandalf’
WHERE id = 4; – Update the table to include Gandalfs Middle Earth ID
SELECT * FROM lotr; – – Checking the row after editing
DELETE FROM lotr WHERE middle_earth IS NULL; – Delete all the rows that have a null value in the lotr column
Logged in a ROOT to MYSQL
Use wp_whatever; – Choose the database
rename table options to wp_options; – How to rename a table to something else.
SELECT * FROM lotr; to show the table
INSERT INTO lotr (id, name, age) VALUES (2, ‘Gimli’, 139); – insert into a table
UPDATE lotr SET id = ‘3’ WHERE name = ‘Gimli’; – change the value from 2 to 3
https://gist.github.com/hofmannsven/9164408 – commands to use in mYSQL
