As Sys admins we are often tasks with working on multiple and disparate systems we need to know a little about everything to allow use to do our jobs effectively. This post will detail my top five MySQL commands to know – with one bonus command! The commands are:
Create database wordpress;
This command creates a database call wordpress.
The Command shows all databases that reside on the server.
Selects the wordpress database for use – all subsequent commands will run the wordpress database.
Shows all tables within the current selected database.
select * from courses;
Displays all information from the courses table.
Bonus Command! grant all on WordPress.* to ‘wordpressuser’@’localhost’ identified by ‘wordpresspass’;
Grants all permissions on the WordPress database from the user wordpressuser from the local machine with the password wordpresspass
If you want to see these commands in action check out the youtube video here:
Also be sure to download the cheetsheet here which you can save and print out:
MariaDB is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. Development is led by some of the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle Corporation.
After you install MariaDB you log in using the following command:
mysql -u root -p
You may notice that you don’t get prompted for a password. In order to correct this you will need to run the following :
SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘yourPassword’);
UPDATE mysql.user SET plugin = ‘ ‘ WHERE user = ‘root’ AND host = ‘localhost’
Run mysql -u root -p You will now need your password to log in.
“MariaDB is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. Development is led by some of the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle Corporation”
This short MariaDB tutorial will show you how to check the version of MariaDB you are running. We are running MariaDB on CentOS 7 but the process should be much the same for other operating systems.
Log into your MariaDB instance, in our case we log in using the command:
mysql -u root -p
After you log in you can see your version in the welcome text – highlighted in the screen-grab below:
If you cannot see your version here you can also run the following command to see it:
A full video tutorial on the process can be found here:
This video shows how to install Own Cloud on Centos. Own Cloud is simailr to dropbox but you install it on your own server which gives you full control over your data. There are several prerequisites needed to follow along with this video, which I have also included below.
I recently installed Moodle 3.0, everything was going to plan until I noticed that Moodle was giving me the warning below:
“Your database has tables using Antelope as the file format. You are recommended to convert the tables to the Barracuda file format. See the documentation Administration via command line for details of a tool for converting InnoDB tables to Barracuda.”
As the warning alludes to, there is a simple script that you can run that will correct the issue for you. However, there is a caveat, you need to give your moodle database user super permissions, something that I did not wish to do.
You can convert your file format by running the commands directly on the DB using your main administrator account.
SET SESSION sql_mode=STRICT_ALL_TABLES;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_file_format=Barracuda;
ALTER TABLE mdl_certificate ROW_FORMAT=Compressed;
ALTER TABLE mdl_data ROW_FORMAT=Compressed;
ALTER TABLE mdl_data_fields ROW_FORMAT=Compressed;
ALTER TABLE mdl_enrol_paypal ROW_FORMAT=Compressed;
ALTER TABLE mdl_lti ROW_FORMAT=Compressed;
ALTER TABLE mdl_user ROW_FORMAT=Compressed;
ALTER TABLE mdl_user_info_field ROW_FORMAT=Compressed;
Once you have ran the above commands the warning on the Moodle site will no longer show.