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...

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

Open a new Terminal Window if not already in Command Line
Type in the command below

Linux
mysql -uusername -ppassword databasename < import.sql

Windows
mysql -u username -p databasename < import.sql

In the CLI (Command Line Interface) type in the command below and hit enter
mysql --version
The results should look similar to the below with the Version Number in the string
mysql  Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (i486) using readline 5.2

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...

E.g. SELECT  * FROM users; or SHOW tables;
In the command line make sure you used the correct syntax when connecting to mySql
Correct example:
mysql -uroot -ppassword
Wrong example: (This is what I did that caused this error)
mysql -rroot -ppassword

For example, if you have a table with 100 rows in it and you need to get 3 random rows then this could come in handy. 

SELECT * FROM ads ORDER BY RAND() LIMIT 3

We’ve used the RAND() function to return random table rows

Use the following command in the Command Line Interface

mysqldump --no-data --tables -uMYSQL_USER_NAME -pMYSQL_PASSWORD MYSQLDATABASE >> FILE_NAME.sql

Replace MYSQL_USER_NAME with your username,  MYSQL_PASSWORD with your password, MYSQLDATABASE with the database name you want to export andFILE_NAME.sql with the file that you wish to export the schema to.

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...

You will need to know the current root password in order to change it
In the command line type in and execute the following command

mysqladmin -u root -pOLD_PASSWORD password NEW_PASSWORD

Replace OLD_PASSWORD with your old password and NEW_PASSWORD with your new password.

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...

RENAME TABLE oldTable TO newTable;

Replace oldTable with the table’s name that you want to rename and newTable with the new table name.

Login to the MySql command line using something like the following

mysql -uUSERNAME -pPASSWORD

Then execute the following command to Drop the desired Database

DROP TABLE MYDATABASE;

Replace MYDATABASE with the Database name that you want to Drop
Note: Please note that this can’t be undone, so please create a backup before proceeding.

After your database has been selected using the use command eg.

use database;

you can type the show tables command to list all tables within the selected databaseeg.

show tables;

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

The code below will do the trick, this line can be modified to accomodate your needs. A where clause can also be added

UPDATE databaseTableName SET tableColumnName = replace(tableColumnName,"findValue","replaceValue");

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...

Select your database via the command line:

USE your_database;

Then execute the following two commands:

show variables like "character_set_database";

and

show variables like "collation_database";

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...