MySQL

Create user and grant permission

1
2
3
4
5
6
7
8
9
10
11
12
13
# Create a database called 'db_foo'
CREATE DATABASE db_foo;
# Create a user 'foo' which only allowed access via 'localhost'
# i.e. mysql -u foo -p
CREATE USER 'foo'@'localhost' IDENTIFIED BY 'password';
# Grant all privileges to user 'foo' only for database 'db_foo'
# if want to allowed for ALL database, just change 'db_foo.*' to '*.*'
GRANT ALL PRIVILEGES ON db_foo.* TO 'foo'@'localhost' WITH GRANT OPTION;
# Create a user 'foo' which allowed access remotely
# i.e. mysql -u foo -h 192.168.1.xxx -p
CREATE USER 'foo'@'%' IDENTIFIED BY 'another_or_same_password';
# Grant all privileges to user 'foo' only for database 'db_foo'
GRANT ALL PRIVILEGES ON db_foo.* TO 'foo'@'%' WITH GRANT OPTION;
Reference:

Dump database to sql file

1
2
$ mysqldump -u root -p database_name > filename.sql
# enter password

-u - refer to user (normally use root)
-p - will prompt you password

Select current DateTime

1
mysql > SELECT NOW();

Output:

1
2
3
4
5
6
+---------------------+
| NOW()               |
+---------------------+
| 2012-12-11 17:28:30 |
+---------------------+
1 row in set (0.00 sec)
Reference:

String concatenation with IF condition

This is to avoid the extra comma appear if some of the component doesn’t exist.

1
2
3
4
5
6
7
8
SELECT CONCAT(
    address
    , IF(address2 IS NULL OR address2 = '', '', CONCAT(', ', address2))
    , IF(address3 IS NULL OR address3 = '', '', CONCAT(', ', address3))
    , IF(postcode IS NULL OR postcode = '', '', CONCAT(', ', postcode))
    , IF(state IS NULL OR state = '', '', CONCAT(', ', state))
) AS full_address
FROM what_ever;
Reference:

Dump a specific table

1
$ mysqldump -u user -p db_name table_name1 table_name2 > file.sql
Reference:

Get a specific opponent from explode equivalent string

1
2
3
4
SELECT SUBSTRING_INDEX(
    SUBSTRING_INDEX('123-456-789-abc-this_is_what_we_want-xyz', '-',5)
    , '-', -1
);

Look at the inner SUBSTRING_INDEX, we explode by - (dash), so the 5 is to take from the 1st to 5th opponent.
So we get 123-456-789-abc-this_is_what_we_want.

Now the outer SUBSTRING_INDEX is to get 1 opponent start from the left, which is the last opponent, is what we want this_is_what_we_want.

Migrate data form table A to table B

1
2
3
4
INSERT INTO table_b (col_1, col_2, col_3)
(SELECT col_1, col_2, col_3
FROM table_a
WHERE col_1 = 'foo');
Reference:

printf like in MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT LPAD(`id` , 4, '0') FROM `table_name` LIMIT 10;
+---------------------+
| LPAD(`id` , 4, '0') |
+---------------------+
| 0001                |
| 0002                |
| 0003                |
| 0004                |
| 0005                |
| 0006                |
| 0007                |
| 0008                |
| 0009                |
| 0010                |
+---------------------+

Which is similar to printf("%04d", value);

Reference:

Copy a table structure from a database to another database

1
mysql> CREATE TABLE source_database.foo_table LIKE dest_database.foo_table;
Reference:

Add root password to MySql in XAMPP

1
2
# initially was no password, so can login directly
$ /Applications/XAMPP/xamppfiles/bin/mysql -u root
1
2
mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
mysql> FLUSH PRIVILEGES;

Edit the phpMyAdmin config, otherwise could not be login

1
$ sudo vi /Applications/XAMPP/xamppfiles/phpmyadmin/config.inc.php

Change the password

1
2
3
4
5
6
...
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = 'MyNewPass'; /* this line */
...
Reference:

Auto-input password on mysql shell command

Typical way, it will prompt for password

1
2
$ mysql -u root -p
Enter password:

Another way, it you passed the password through the input argument

1
2
3
# username: root
# password: MyAwesomePassword
$ mysql -uroot -pMyAwesomePassword
Reference:

Rename a column with FULLTEXT index

1
2
3
4
5
6
7
ALTER TABLE tablename ADD column_new_name VARCHAR(100) AFTER column_old_name;
ALTER TABLE tablename MODIFY column_new_name VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci; /* make sure the collation is same as column_old_name */
/* copy the data from column_old_name to column_new_name */
UPDATE tablename SET column_new_name = column_old_name;
ALTER TABLE tablename ADD FULLTEXT(column_new_name);
/* Delete old column */
alter TABLE tablename DROP COLUMN column_old_name;

Update all dates by addeing 30 minutes

1
UPDATE `tablename` SET `created_date` = DATE_ADD(`created_date` , INTERVAL 30 MINUTE ) WHERE `column` = 'value'
Reference:

Check is MySQL user exists

1
2
3
SELECT `User`, `Host`
FROM `mysql`.`user`
WHERE `User` = 'foouser'
Reference:

Row size too large

Error shown is

1
SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

The table here contains many columns with TEXT data type, each column has a long long paragraph, thus this error rised

Solution

Edit the file my.cnf (for XAMPP in Mac is on /Applications/XAMPP/xamppfiles/etc/my.cnf)

1
2
innodb_file_per_table
innodb_file_format = Barracuda

Add the content above to [mysqld] section. Then run sql

1
ALTER TABLE myawesome_table ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

Restart XAMPP server

Reference:

Date ‘0000-00-00’ error on MySQL 5.7

Cannot even update the value

1
2
3
UPDATE table SET birthday = null WHERE birthday = '0000-00-00';

#1292 - Incorrect date value: '0000-00-00' for column 'birthday' at row 1

But it works in this way

1
UPDATE table SET birthday = null WHERE CAST(birthday AS CHAR(10)) = '0000-00-00';
Reference:

Export a query to a csv file

1
mysql -h 123.123.123.123 -u db_username -p db_name < custom-query.sql > output.tsv
Reference:

MariaDB

mysqld start failed

1
2
$ sudo rm /var/lib/mysql/{ib_logfile0,ib_logfile1,aria_log_control}
$ sudo /etc/init.d/mysql restart
Reference: