Daily MySQL 8.0 Administration

List of user available

SELECT User, Host FROM mysql.user;

Show privileges of an account

show grants for exampleuser@localhost;

Contoh:

mysql> show grants for epintaruser@localhost;

+------------------------------------------------------------------+
| Grants for epintaruser@localhost |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `epintaruser`@`localhost` |
| GRANT ALL PRIVILEGES ON `epintar`.* TO `epintaruser`@`localhost` |
+------------------------------------------------------------------+
2 rows in set (0.00 sec)

Alter user password

ALTER USER 'userName'@'localhost' IDENTIFIED BY 'New-Password-Here';

Contoh:

mysql> alter user 'moodle_user'@'%' identified by 'p4sswordku';

Query OK, 0 rows affected (0.00 sec)

mysql>

Grant privileges of a database to a user

Syntax:

GRANT <privileges> ON <database>.<object> TO '<user>'@'<host>';

Sebelumnya harus sudah ada terlebih dahulu proses pembuatan user dengan host yang sama

Contoh:

mysql> GRANT ALL PRIVILEGES ON digitoskelasku.* TO 'moodle_user'@'%';
Query OK, 0 rows affected (0.00 sec)

mysqldump in practice

Default

Kasus #1

# mysqldump --no-create-info --complete-insert --insert-ignore pintar_db > pintar_db-20230824-2208.insert-ignore.data

File backup yang dihasilkan:

  1. tidak memuat perintah CREATE [table “” not found /]
  2. kalau ada data yang akan di insert, ternyata sudah ada, maka proses insert record data terkait akan di lewatkan
  3. complete-insert …

Kasus #2 – replace

mysqldump --no-create-info --replace pintar_db > pintar_db-20230827-1554.replace.data

File backup yang dihasilkan:

  1. tidak memuat perintah CREATE [table “” not found /]
  2. kalau ada data yang akan di insert, ternyata sudah ada, maka data tersebut akan direplace oleh data yang baru datang

Kasus #3 – dump table tertentu

Jika Anda akan dumping tabel-tabel t1, t2, dan t3 dari mydb:

mysqldump -u... -p... mydb t1 t2 t3 > mydb_tables.sql

Sebaliknya, Jika Anda akan dumping semua tabel kecuali tabel-tabel t1, t2, dan t3 dari mydb:

DBTODUMP=mydb
SQL="SET group_concat_max_len = 10240;"
SQL="${SQL} SELECT GROUP_CONCAT(table_name separator ' ')"
SQL="${SQL} FROM information_schema.tables WHERE table_schema='${DBTODUMP}'"
SQL="${SQL} AND table_name NOT IN ('t1','t2','t3')"
TBLIST=`mysql -u... -p... -AN -e"${SQL}"`
mysqldump -u... -p... ${DBTODUMP} ${TBLIST} > mydb_tables.sql

Note: Anda dapat mengubah SQL="${SQL} AND table_name NOT IN ('t1','t2','t3')" to SQL="${SQL} AND table_name NOT LIKE 'foo\_%'". Anda juga dapat mengganti kondisi menjadi ‘%foo%’ untuk mendapatkan semua table yang menggandung kosa-kata ‘foo’ dalam nama tabelnya (termasuk didalamnya seperti: ‘food’, ‘fool’, dst).

Kasus #4 – ignore table(s)

Cara lain jika Anda akan dumping semua tabel kecuali tabel-tabel t1, t2, dan t3 dari mydb:

mysqldump -uusername -ppassword dbname \
  --ignore-table=schema.t1    \
  --ignore-table=schema.t2    \
  --ignore-table=schema.t3 > mysqldump.sql

File backup yang dihasilkan:

  1. Semua table di dump tapi tidak akan dumping table t1,t2 dan t3

References:

  1. https://man7.org/linux/man-pages/man1/mysqldump.1.html
  2. https://dba.stackexchange.com/questions/9306/how-do-you-mysqldump-specific-tables

MySQL: Duplicate table

Duplikasi struktur table nya saja tanpa data

CREATE TABLE new_table LIKE original_table;

Perintah di atas akan membuat duplikat struktur table new_table yang sama persis dengan original_table.

Untk menyalin datanya dapat menggunakan perintah sebagai berikut:

INSERT INTO new_table SELECT * FROM original_table;

Reference: https://popsql.com/learn-sql/mysql/how-to-duplicate-a-table-in-mysql

Insert MySQL Table from *.csv file

Setup local infile

Sebelum dapat melakukan load data secara local ke dalam database, maka perlu memastikan bahwa load infile dalam kondisi aktif

Periksa status local infile

mysql> show global variables like 'local_infile';

Akan memberikan informasi seperti di bawah ini:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

Untuk mengaktifkan gunakan perintah berikut:

mysql> set global local_infile=true;

Setelah itu silahkan connect ke database dengan parameter local infile

mysql --local_infile=1 -u root -ppassword DB_name

Dan lakukan load data local, seperti:

mysql> load data local infile 'path/file_name.extention' into table table_name;

INSERT

Misalkan kita memiliki table berikut ini:

mysql> desc temp_user_info_data;
+------------+------------+------+-----+---------+----------------+
| Field      | Type       | Null | Key | Default | Extra          |
+------------+------------+------+-----+---------+----------------+
| id         | bigint(10) | NO   | PRI | NULL    | auto_increment |
| userid     | bigint(10) | NO   | MUL | 0       |                |
| fieldid    | bigint(10) | NO   |     | 0       |                |
| data       | longtext   | NO   |     | NULL    |                |
| dataformat | tinyint(2) | NO   |     | 0       |                |
+------------+------------+------+-----+---------+----------------+

Catatan: untuk melihat info lebih detail tentang table dan bagaimana table tersebut di buat, Anda dapat menggunakan perintah:

mysql> desc namatable

atau

mysql> show create table namatable;

Kemudian file data kita diletakkan di /root dengan nama file data *.csv: contoh.csv seperti berikut:

5791,23,11
19766,23,11

Lalu lakukan load data:

mysql> load data local infile '/root/contoh.csv' into table temp_user_info_data fields terminated by ',' (userid,fieldid,data);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

Jika data Anda memiliki satu baris pertama yang berisi nama kolom, maka berikan ignore 1 lines seperti berikut:

mysql> load data local infile '/root/contoh.csv' into table temp_user_info_data fields terminated by ',' ignore 1 lines (userid,fieldid,data);

Fix Error #551 in phpMyAdmin 18.04

Error message containts :

Warning in ./libraries/plugin_interface.lib.php#551
count(): Parameter must be an array or an object that implements Countable

Solution:

You can also fix this by editing the library itself.

  1. Make a backup first
    sudo cp /usr/share/phpmyadmin/libraries/plugininterface.lib.php /usr/share/phpmyadmin/libraries/plugininterface.lib.php.bak
  2. Edit the library
    sudo nano /usr/share/phpmyadmin/libraries/plugin_interface.lib.php
  3. Search for “if (! is_null($options) && count($options) > 0) {”
    or if not found then search for “if ($options != null && count($options) > 0) {”

Then replace it with “if ($options != null) {”

To search in Nano editor press CTRL and W

  1. Save the file (CTRL and O)
  2. Refresh the phpMyAdmin page and try agin.

Backup Stored Procedures and Routines

We need to specify --routines to take backup of stored procedures with data and tables.

The following command will take backup of entire database including stored procedures. For example, your database name is “mydb”.

mysqldump -u root -p –routines mydb > mydb.sql

To take backup of only Stored Procedures and Triggers (Exclude table and data ) use the following command.

mysqldump –routines –no-create-info –no-data –no-create-db –skip-opt mydb > mydb.sql

Reference:
https://tecadmin.net

Mencopot MySQL sepenuhnya

Uninstall MySQL dengan apt-get remove terkadang tidak lah cukup, karena tidak menghapus jejak-jejak sepenuhnya.

Berikut perintah-perintah yang lebih ‘gahar’, tapi mohon hati-hati ya, jangan sampai data Anda hilang kalau belum di backup :

sudo -i
service mysql stop
killall -KILL mysql mysqld_safe mysqld
apt-get --yes purge mysql-server mysql-client
apt-get --yes autoremove --purge
apt-get autoclean
deluser --remove-home mysql
delgroup mysql
rm -rf /etc/apparmor.d/abstractions/mysql /etc/apparmor.d/cache/usr.sbin.mysqld /etc/mysql /var/lib/mysql /var/log/mysql* /var/log/upstart/mysql.log* /var/run/mysqld
updatedb
exit

 

MySQL Create Database with utf8mb4 Character Set Syntax

Create database

I frequently create database with utf8mb4 character set:

CREATE DATABASE 'mydb' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; GRANT ALL ON 'mydb'.* TO 'username'@localhost IDENTIFIED BY 'password'; FLUSH PRIVILEGES;

or

CREATE SCHEMA 'mydb' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; GRANT ALL ON 'mydb'.* TO 'username'@localhost IDENTIFIED BY 'password'; FLUSH PRIVILEGES;

Create user

Create user and give privilege to it

create user 'username'@localhost identified by 'password'; grant all privileges on *.* to username@localhost; 
or 
GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;

Change user’s password

Replace the password with the password that you want to use.

MySQL 5.7.6 and later:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

MySQL 5.7.5 and earlier:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');

Here is a short list of other common possible permissions that users can enjoy.

  • ALL PRIVILEGES- as we saw previously, this would allow a MySQL user all access to a designated database (or if no database is selected, across the system)
  • CREATE- allows them to create new tables or databases
  • DROP- allows them to them to delete tables or databases
  • DELETE- allows them to delete rows from tables
  • INSERT- allows them to insert rows into tables
  • SELECT- allows them to use the Select command to read through databases
  • UPDATE- allow them to update table rows
  • GRANT OPTION- allows them to grant or remove other users’ privileges

Reference from :

  • http://www.euperia.com/development/mysql/mysql-create-database-with-utf8-character-set-syntax/1064
  • https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql

MySQL Create Database with UTF8 Character Set Syntax

I always forget the MySQL create database with UTF8 character set syntax, so here it is:

CREATE DATABASE `mydb` CHARACTER SET utf8 COLLATE utf8_general_ci;
GRANT ALL ON `mydb`.* TO `username`@localhost IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Alternatively, you can use ‘CREATE SCHEMA’ instead of ‘CREATE DATABASE’:

CREATE SCHEMA `mydb` CHARACTER SET utf8 COLLATE utf8_general_ci;
GRANT ALL ON `mydb`.* TO `username`@localhost IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

I hope this helps someone else too!

(*resource : here

Access MySQL remotely

Object : MySQL versi 5.5 on Ubuntu 12.10

Terkadang kita memiliki database MySql yang tidak menyatu IP nya dengan server aplikasi, maka server database perlu dipersiapkan agar dapat diakses secara remote.
Yang perlu kita konfigurasi adalah file my.cnf yang biasanya terletak di /etc/mysql/.
Bukalah file tersebut lalu cari dan ganti :
bind-address = 127.0.0.1
menjadi
bind-address = 0.0.0.0
Simpanlah file tersebut, kemudian restart server MySQL nya :

sudo /etc/init.d/mysql restart

Untuk memastikan bahwa MySQL server sudah bisa diakses dari jaringan, coba cek dengan perintah :

netstat -anp | grep 3306

jika Anda menemukan teks seperti di bawah ini :

tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN     -

Ini artinya MySQL kita sudah bisa diakses dari jaringan.
Kemudian langkah terakhir adalah memberikan kewenangan pada user siapa saja akses ini bisa gunakan, dengan cara mengatur previleges user :
mysql> GRANT ALL PRIVILEGES ON *.* TO <username>@'%' IDENTIFIED BY '<password>';
Gantilah <username> dan <password> dengan yang sesuai Anda inginkan.
Semoga berhasil, Tetap Semangat! TS!

1 2