How to format date fields using MySQL


Ever wondered how to change the output of the date format using MySQL? If you are constantly logging you’re data it’s more likely that you will use DATETIME fields in a MySQL table field to track the dates of events. What happens if you need to display the date in Human readable format? By using the DATE_FORMAT() function you’ll be able to manipulate the Date in almost any...

How to backup a MySQL database in Ubuntu


Open a new Terminal Window.
Type in the following command:
mysqldump -h localhost -u root -pPASSWORD database > backup.sql
This will backup the database to the file backup.sql
-h = Hostname
-u = Username
-p = Password, no space between the p and the password allowed

NOTE: Has been tested on Ubuntu 9.04 and mySql 5.0.75

How to export a MySql database to a file via the Command Line in Linux


Open a new Terminal Window Type in the following command {codecitation class=”brush: bash;”} mysqldump -uUsername -pPassword DatabaseName > outputFile.sql {/codecitation} The command above will export a selected database to a file called outputFile.sql mysqldump = a database backup program -u = Username for the database -p = Password for the database DatabaseName = The database...

How-to create a MySQL database and assign user privileges to it


NOTE: Please note that this is done in the Command Line Interface. First login to the mySql server using root. mysql -uroot -pROOT_PASSWORD Replace ROOT_PASSWORD with the password for the root user. Once logged in we create a new database called TEST_DB replace with your database name create database TESTDB; Query OK, 1 row affected (0.00 sec) We grant access privileges to user MYUSER to connect...

ERROR 1194 (HY000): Table ‘mytable’ is marked as crashed and should be repaired, how do I fix this?


Error: 1194 SQLSTATE: HY000: means ER_CRASHED_ON_USAGE In the mySql command line you should run the following command to repair the table repair table mytable; //Replace mytable with your table name REPAIR TABLE  repairs a possibly corrupted table. To read more about the REPAIR TABLE command click here. NOTE: If you do not have access to the command line then phpMyAdmin should have a repair table...

How to output my MySql query results to a CSV file on the command line


Type in the following command

SELECT id,clientid,company FROM config INTO OUTFILE '/tmp/List.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

The command above will output the results from the MySql query into a Comma Separated File called List.csv, it will split the columns by using a comma “,” and will terminate every new line with the line break character \n

How to use the CONCAT MySql function to concatenate 2 table columns


The CONCAT function can be used to concatenate two strings to form a single string or two columns to form a single column. Say for Example you have a table users with columns name and surname and you would like to display both as one name you would use the CONCAT function to join the two fields as in the Example below SELECT CONCAT(name," ",surname) AS fullname FROM users; The above code will...

Table ‘performance_schema.session_variables’ doesn’t exist


Solution 1 Upgrade your MySql server. After the upgrade reboot the server mysql_upgrade -u root -p --force systemctl restart mysqld Solution 2 This solution is not permanent. After the next reboot of the MySql server all changes will be lost. mysql -u app -p mysql> set @@global.show_compatibility_56=ON; Solution 3 This solution is permanent. Locate and edit your MySql config file: my.cnf Under...

About Author

Ian Carnaghan

I am a software developer and online educator who likes to keep up with all the latest in technology. I also manage cloud infrastructure, continuous monitoring, DevOps processes, security, and continuous integration and deployment.

Follow Me