MySQL stuff 1

If you delete /var/log/mysql by mistake, probably you’d be unable to start mysql. In such a situation, when you type

service mysql status 

you could get a message like this:

● mysql.service - MySQL Community Server
   Loaded: loaded (************************)
   Active: failed (Result: exit-code) since ******

I just addressed this issue by typing

sudo makdir /var/log/mysql
sudo chown -R mysql:mysql /var/log/mysql
service mysql restart

Then if you check the status of mysql, you see Active is active (running).

Alter some of a table

To change the name of a table into TBL1, add a column (where the name of the table is A0) (after Column CD), rename the new column (into B), change the data type of the column into varchar(5), and delete the new column, you type

alter table ... rename to TBL1;
alter table ... add column A0 int after CD;
alter table ... change column A0 B int; 
alter table ... modify B varchar(5);
alter table ... drop B;

In MySQL, you can select the position where a new column is created — in this case, the column A0 was inserted after CD. In PostgreSQL, a newly added column always comes last.

Insert Null values

In Oracle, insert … values (‘’, …) allows us to insert a null value into a column of table tblA. In MySQL, a null value must be explicitly written:

insert into tblA value (Null, ...);  

Autocommit

In Oracle, show autocommit shows whether autocommit is on or off, in MySQL, the equivalent command should be

show variables like 'autocomm%';

You’d get some thing like

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

Save and load databases

Database dbA is exported to testDB.sql file by

mysqldump -u [username] -p --databases dbA > testDB.sql

where [username] is a user name for the databases. And you can load the backup file by:

mysqldump -u [username] -p dbA < testDB.sql 

Export to .csv file

You can also export selected data into .csv file by

select * from tblA into outfile 'testA.csv';

But you could encounter the following error message

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Then you search the directory of secure_file_priv, and you can put the exported file in the directory.

show variables like 'secure_file%'
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | sql_directory         |
+------------------+-----------------------+
select * from tblA into outfile 'sql_directory/testA.csv';