All About MySQL

This is a page which contains list of articles related to MySQL Database ServerĀ  :

Tips for Access

  1. How to Access MySQL Database Server via Command Line .
  2. How to Remote Execute MySQL Query via Command Line .

Tips for Checking MySQL Version

How to Check MySQL Version in Linux via Command Line .

Tips for Backup MySQL Database

How to Create Daily Backup MySQL Database using Cron in Linux .

Tips for Retrieving Data

  1. How to Export MySQL Query Result to CSV via Command Line .
  2. How to Retrieve Result from Remote Executable MySQL Database Query to File .

Tips for Column Management

  1. How to Change Column Order in MySQL Database Server via Command Line.
  2. How to drop column in MySQL Database Server .

Tips for Table Management

  1. How to Create Additional Table for Many to Many Relationship in MySQL Database Server .
  2. How to Alter Table Add Field after Another Field MySQL Database Server via Command Line with Example .
  3. How to Alter Table Add Primary Key in MySQL Database Server via Command Line with Example .

Tips for Foreign Key

  1. How to Add Foreign Key in MySQL Database Server via Command Line .
  2. How to Drop Foreign Key in MySQL Database Server via Command Line .

Tips for Privileges Management

  1. How to Show Privileges in MySQL Database Server via Command Line .
  2. How to Show Privileges of a User in MySQL Database Server via Command Line .
  3. How to Revoke Privilege in MySQL Database Server .

Tips for User Management

How to Drop User in MySQL Database Server .

Tips for Showing Create Script

How to Show Create Script in MySQL Database Server via Command Line .

Error

  1. How to solve MySQL Error Message : ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2) .
  2. How to solve MySQL Error Message : There can be only one auto column and it must be defined as a key .
  3. How to solve MySQL Error Message Multiple primary key defined .
  4. How to get SQL Query history in MySQL .
  5. How to solve MySQL Database Generate ERROR 1064 (42000): You have an error in your SQL syntax Add Foreign Key .
  6. How to solve MySQL Error Message Cannot add or update a child row: a foreign key constraint fails .
  7. How to solve MySQL Error Failed to dump MySQL Database /tmp file permission denied .
  8. How to solve MySQL Error Cannot Change root Password MySQL using MySQL console .
  9. How to Solve MySQL Error Database generate ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails .
  10. How to Solve MySQL Error Database generate ERROR 1215 (HY000): Cannot add foreign key constraint .
  11. How to Solve MySQL Error Dumping Database on MySQL Database Definer does not Exist .
  12. How to Solve MySQL Error Message : ERROR 1114 (HY000) at line 4032: The table ‘table’ is full .
  13. How to Solve MySQL Error Message : ERROR 1010 (HY000): Error dropping database (can’t rmdir ‘./db’, errno: 39) .

How to Drop Column in MySQL via Command Line

This is an article where the focus is about executing a specific query in MySQL Database Server. The specific query itself is a query to drop column in MySQL Database Server. In order to drop a specific column in a MySQL Database Server, the following are steps to be able to achieve it via command line :

1. Make sure MySQL Database Server has already installed.

2. Make sure the service of MySQL Database Server is active by checking the status of the service whether it is running or not. To be able to check it, just read the following article titled ‘Check MySQL Service Status’ in this link.

Continue reading “How to Drop Column in MySQL via Command Line”

MySQL Add Foreign Key in MySQL Command Console

This is an article which is specifically written to show how to add foreign key in MySQL Database Server. There are lots of means or utilities which can be utilized in order to add foreign key. In MySQL Database Server, add foreign key can be done by executing a certain query in MySQL Command Console or in a MySQL Editor based on GUI.

The query which is specifically executed to add a foreign key in a table can be shown in the following pattern :

Continue reading “MySQL Add Foreign Key in MySQL Command Console”

Show Create Table Script in MySQL

This is an article written simply to show how to display a script for creating a table in MySQL Database Server. The table itself has already been created and the script itself is useful for recreating the table if there is really a purpose to do that in other occasion or simply try to assess and to look the component of the table in details. For an example to look at the component of the table whether it contains a specific foreign key column which refers to another column exist in another table.

To achieve the purpose in the context of this article, it can simply be done using the help of MySQL GUI Editor such as MySQL Workbench, Navicat or any multipurpose SQL GUI Editor available in the market. But for this article context, it is going to be presented in the form of text-based query executed in MySQL Command Console. Below are steps needed to be taken to aim for the goal :

Continue reading “Show Create Table Script in MySQL”

Change Column Order MySQL via Command Line

Change Column Order MySQL via Command Line

This is an article which is made to show to alter a table in MySQL Database Server with the specific aim which is to change column order in MySQL database server via command line. The scenario is where a table exist with the order of the fields seems not right and it is going to be changed as shown below :

mysql> desc table_test;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| name      | varchar(255) | NO   |     | NULL    |       |
| id_table  | int(11)      | NO   | PRI | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
2 rows in set (0,00 sec)

mysql>

The above table is actually derived from an example created in an article named ‘Alter Table Add Primary Key MySQL Example via Command Line’ which can be accessed in this link. The primary concern is when adding a primary key field after other existing table might be uncomfortable to be read because normally a primary key field will be located in the first field order in a table.

So, to alter it into a correct order, for instance to move the field ‘id_table’ into the first order since maybe that field is a primary key field and it is normally that kind of field located in the first order. Below is the syntax which can be used to reorder the sequence of the field on the table :

alter table server_test change name name varchar(255) after id_table;

Below is the execution of the command above in a real situation :

Continue reading “Change Column Order MySQL via Command Line”

Alter Table Add Primary Key MySQL Example via Command Line

Alter Table Add Primary Key MySQL Example via Command Line

This is an article which is created to change a table exist in MySQL Database Server. The change which is going to be achieved is by adding a primary key to the table. This is actually happens when a created table didn’t have a field acts as a primary key. Adding a new field and then assign it as a primary key will make itself as an attempt to change or to alter the structure of a table.

The scenario for implementing on adding a new primary key on a table which is actually just create a simple table without any primary key and then altering it by adding a new primary key. So, these are the steps done to show how to add primary key field in a table :

1. Create a new table. Below is actually the syntax which can be executed in MySQL console’s command line to achieve it :

mysql> create table table_test(name varchar(255) not null);
Query OK, 0 rows affected (0,11 sec)

mysql> desc table_test;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(255) | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0,01 sec)

mysql> 

Continue reading “Alter Table Add Primary Key MySQL Example via Command Line”