MySQL: Difference between revisions
(115 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
= Installation issues = | |||
== Fail to start == | |||
On Ubuntu 12.04, we can use | On Ubuntu 12.04, we can use | ||
<pre> | <pre> | ||
Line 6: | Line 6: | ||
</pre> | </pre> | ||
== InnoDB vs MyISAM == | |||
The default is InnoDB. | The default is InnoDB. | ||
= Under Shell = | |||
== Manual/Documentation == | |||
https://dev.mysql.com/doc/refman | https://dev.mysql.com/doc/refman | ||
=== Create a new user account to grant access to a database | == MariaDB vs MySQL == | ||
* [https://www.guru99.com/mariadb-vs-mysql.html MariaDB vs MySQL: Key Performance Differences] | |||
* [https://www.eversql.com/mariadb-vs-mysql/ MariaDB vs MySQL – Key Differences – Comparing MySQL 8.0 with MariaDB 10.5] | |||
* [https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ MariaDB versus MySQL: Compatibility] | |||
== Check server version == | |||
[https://www.heelpbook.net/2019/check-mysql-and-mariadb-server-version/ Check mySQL and MariaDB Server Version] | |||
== Installation, setup root password, remove the need of 'sudo' == | |||
* [https://www.howtoforge.com/tutorial/debian_mysql/ How to install MySQL 8 on Debian 10]. It includes how to secure it and how to back up. | |||
* [https://websiteforstudents.com/installing-mariadb-database-server-on-ubuntu-18-04-lts-beta-server/ Installing MariaDB Database Server On Ubuntu 18.04 LTS Server] | |||
* [https://linuxize.com/post/how-to-install-mariadb-on-ubuntu-18-04/ How to Install MariaDB on Ubuntu 18.04] | |||
* [https://www.howtoforge.com/how-to-install-wallabag-on-ubuntu-1804/#install-mariadb How to Install Wallabag on Ubuntu 18.04 LTS] | |||
* [https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-22-04 How to install MySQL server on Ubuntu 22.04 LTS Linux] | |||
* [https://www.howtoforge.com/tutorial/perfect-server-ubuntu-18.04-with-apache-php-myqsl-pureftpd-bind-postfix-doveot-and-ispconfig/ The Perfect Server - Ubuntu 18.04 (Bionic Beaver) with Apache, PHP, MySQL, PureFTPD, BIND, Postfix, Dovecot and ISPConfig 3.1]. Edit '''/etc/mysql/debian.cnf''' file and set the root password there in addition to setting the root password by mysql_secure_installation. | |||
* [https://www.cyberciti.biz/faq/how-to-create-mysql-admin-user-superuser-account/ How to create MySQL admin user (superuser) account] MySQL 8.x or MariaDB 10.4+ | |||
Debian 9 (Stretch) package now ships with the '''UNIX_SOCKET''' authentication ''plugin'' enabled and you are no longer asked to set a root password when installing the package. Even when setting a root password via the '''mysql_secure_installation''' script you are still denied. So we need to run 'update'; see the command below. | |||
See a solution at [https://y0b.org/index.php/2017/08/28/debian-9-stretch-and-mysql-mariadb-root-password/ Debian 9 “Stretch” and MySQL/MariaDB root password]. | |||
<pre> | |||
$ sudo apt-get install mysql-server | |||
$ sudo systemctl status mariadb | |||
$ sudo systemctl start mariadb.service | |||
$ sudo systemctl enable mariadb.service | |||
$ sudo mysql_secure_installation # type the new root password for your MariaDB server and | |||
# type 'Y' for all configurations | |||
Enter current password for root (enter for none): | |||
Set a root password? [y/n] y | |||
Remove anonymous users? [y/n] y | |||
Disallow root login remotely? [y/n] y | |||
Remove test database and access to it? [y/n] y | |||
Reload privilege tables now? [y/n] y | |||
$ sudo mysql -u root mysql -e "update user set plugin='' where user='root'; flush privileges;" | |||
## With the above command, I don't need 'sudo' in the next command | |||
$ mysql -u root -p | |||
... | |||
Welcome to the MariaDB monitor. Commands end with ; or \g. | |||
Your MariaDB connection id is 12 | |||
Server version: 10.1.23-MariaDB-9+deb9u1 Raspbian 9.0 | |||
... | |||
MariaDB [(none)]> show databases; | |||
+--------------------+ | |||
| Database | | |||
+--------------------+ | |||
| information_schema | | |||
| mysql | | |||
| performance_schema | | |||
+--------------------+ | |||
3 rows in set (0.02 sec) | |||
MariaDB [(none)]> exit | |||
Bye | |||
</pre> | |||
=== Access denied for user 'root'@'localhost' === | |||
* https://superuser.com/questions/603026/mysql-how-to-fix-access-denied-for-user-rootlocalhost | |||
* [https://dev.to/joshualjohnson/tutorial--after-installing-mysql-on-ubutnu-1804-fix-access-denied-for-user-rootlocalhost-error-2cib Tutorial – After Installing MySQL on Ubutnu 18.04, Fix “Access denied for user ‘root’@’localhost'” Error] | |||
<pre> | |||
sudo service mysql stop | |||
sudo mkdir -p /var/run/mysqldsudo chown mysql:mysql /var/run/mysqld | |||
sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking & | |||
mysql -u root | |||
mysql> FLUSH PRIVILEGES; | |||
mysql> USE mysql; | |||
mysql> UPDATE user SET authentication_string=PASSWORD("MyPASSWORDHERE") WHERE User='root'; | |||
mysql> UPDATE user SET plugin="mysql_native_password" WHERE User='root'; | |||
mysql> quit | |||
sudo pkill mysqld | |||
sudo service mysql start | |||
</pre> | |||
''MyPASSWORDHERE'' can be as short as 2 characters. | |||
=== Reset root password === | |||
https://www.digitalocean.com/community/tutorials/how-to-reset-your-mysql-or-mariadb-root-password | |||
=== Installing MariaDB Binary Tarballs === | |||
* https://mariadb.com/kb/en/library/installing-mariadb-binary-tarballs/ | |||
* [https://linuxize.com/post/how-to-install-mariadb-on-ubuntu-18-04/ Installing MariaDB on Ubuntu 18.04 from the MariaDB Repositories] | |||
=== Authentication === | |||
* MySQL 8.0. [https://stackoverflow.com/questions/49963383/authentication-plugin-caching-sha2-password "Authentication plugin 'caching_sha2_password'] | |||
* [https://chrisshennan.com/blog/fixing-authentication-plugin-cachingsha2password-cannot-be-loaded-errors Fixing "Authentication plugin 'caching_sha2_password' cannot be loaded" errors] - revert to the mysql_native_password mechanism. | |||
* [https://dev.mysql.com/blog-archive/upgrading-to-mysql-8-0-default-authentication-plugin-considerations/ Upgrading to MySQL 8.0 : Default Authentication Plugin Considerations] | |||
* [https://mariadb.com/kb/en/authentication-plugin-sha-256/ Authentication Plugin - SHA-256] | |||
== Uninstall == | |||
<pre> | |||
sudo service mysql stop | |||
sudo apt-get --purge remove "mysql*" | |||
sudo mv /etc/mysql/ /tmp/mysql_configs/ | |||
sudo reboot | |||
</pre> | |||
== How do I turn off the mysql password validation? == | |||
Normally a password as short as 3 characters is enough for the root user. | |||
* https://stackoverflow.com/questions/36301100/how-do-i-turn-off-the-mysql-password-validation | |||
== Create a new local user account to grant access to a database == | |||
http://www.cyberciti.biz/faq/mysql-user-creation/ | http://www.cyberciti.biz/faq/mysql-user-creation/ | ||
Line 32: | Line 135: | ||
<pre> | <pre> | ||
mysql> GRANT ALL ON testdb.* TO user1@localhost IDENTIFIED BY 'mypassword'; | mysql> GRANT ALL ON testdb.* TO user1@localhost IDENTIFIED BY 'mypassword'; | ||
</pre> | |||
If we quit mysql and log in again using the new account, the 'SHOW DATABASES' will only show databases that the new account can access | |||
<pre> | |||
mysql> show databases; | |||
+--------------------+ | |||
| Database | | |||
+--------------------+ | |||
| information_schema | | |||
| testdb | | |||
+--------------------+ | |||
2 rows in set (0.00 sec) | |||
</pre> | </pre> | ||
Line 41: | Line 156: | ||
</pre> | </pre> | ||
=== MYSQL commands | === Create Remote MySQL user and grant remote access to databases === | ||
[https://www.configserverfirewall.com/ubuntu-linux/mysql-allow-remote-connections/ How to Allow MySQL remote connections in Ubuntu Server 18.04] | |||
== Show all users == | |||
<pre> | |||
SELECT User, Host, Password FROM mysql.user; | |||
SELECT DISTINCT User FROM mysql.user; | |||
</pre> | |||
== [https://dev.mysql.com/doc/refman/5.7/en/show-grants.html Show the privileges of a user] == | |||
<pre> | |||
mysql> SHOW GRANTS FOR 'user1'@'localhost'; | |||
+--------------------------------------------------------------------------------------------------------------+ | |||
| Grants for user1@localhost | | |||
+--------------------------------------------------------------------------------------------------------------+ | |||
| GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF' | | |||
| GRANT ALL PRIVILEGES ON `testdb`.* TO 'user1'@'localhost' | | |||
+--------------------------------------------------------------------------------------------------------------+ | |||
2 rows in set (0.00 sec) | |||
</pre> | |||
Another way is through '''phpMyAdmin'''. Click on 'Databases' and in the Action column click 'Check Privileges' in the corresponding row of the desired database. | |||
== [https://dev.mysql.com/doc/refman/5.7/en/drop-user.html Remove an account] == | |||
<pre> | |||
DROP USER 'jeffrey'@'localhost'; | |||
</pre> | |||
== [https://dev.mysql.com/doc/refman/5.7/en/drop-database.html Remove a database] == | |||
<syntaxhighlight lang='sql'> | |||
mysql> DROP DATABASE db_name; | |||
mysql> SHOW DATABASES; | |||
</syntaxhighlight> | |||
== Show the current user == | |||
<syntaxhighlight lang='sql'> | |||
mysql> SELECT USER(); | |||
</syntaxhighlight> | |||
== Show the current database == | |||
<syntaxhighlight lang='sql'> | |||
mysql> SELECT DATABASE(); | |||
</syntaxhighlight> | |||
Note on MariaDB it shows the current database. | |||
<syntaxhighlight lang='sql'> | |||
MariaDB [(none)]> use mysql | |||
Reading table information for completion of table and column names | |||
You can turn off this feature to get a quicker startup with -A | |||
Database changed | |||
MariaDB [mysql]> | |||
</syntaxhighlight> | |||
== How do you deselect MySQL database? == | |||
https://stackoverflow.com/questions/27895328/how-do-you-deselect-mysql-database. Ans: You just select another database and USE it. | |||
== Version vs Distrib number of MySQL == | |||
https://stackoverflow.com/questions/8645184/version-vs-distrib-number-of-mysql | |||
* '''Ver''' refers to the version of the mysql command line client - what you are envoking by typing 'mysql' | |||
* '''Distrib''' refers to the mysql server version your client was built with. This is not to be confused with the mysql server you are connected to, which can be obtained with SELECT VERSION(); | |||
On Ubuntu 14.04 | |||
<pre> | |||
$ mysql --version | |||
mysql Ver 14.14 Distrib 5.5.58, for debian-linux-gnu (x86_64) using readline 6.3 | |||
$ mysql -u root -p -e 'select version()' | |||
Enter password: | |||
+-------------------------+ | |||
| version() | | |||
+-------------------------+ | |||
| 5.5.58-0ubuntu0.14.04.1 | | |||
+-------------------------+ | |||
</pre> | |||
On Docker's MySQL | |||
<pre> | |||
# mysql --version | |||
mysql Ver 14.14 Distrib 5.7.20, for Linux (x86_64) using EditLine wrapper | |||
# mysql --user=root --password=$MYSQL_ROOT_PASSWORD -e 'select version()' | |||
mysql: [Warning] Using a password on the command line interface can be insecure. | |||
+-----------+ | |||
| version() | | |||
+-----------+ | |||
| 5.7.20 | | |||
+-----------+ | |||
</pre> | |||
== MYSQL commands == | |||
* [https://dev.mysql.com/doc/refman/5.7/en/mysql-command-options.html mysql command line options] | * [https://dev.mysql.com/doc/refman/5.7/en/mysql-command-options.html mysql command line options] | ||
* http://www.pantz.org/software/mysql/mysqlcommands.html | * http://www.pantz.org/software/mysql/mysqlcommands.html | ||
Line 51: | Line 257: | ||
</pre> | </pre> | ||
== Executing SQL Statements from a Text File == | |||
https://dev.mysql.com/doc/refman/5.7/en/mysql-batch-commands.html | * [https://dev.mysql.com/doc/mysql-security-excerpt/5.7/en/user-names.html Account User Names and Passwords] | ||
* [https://dev.mysql.com/doc/refman/5.7/en/mysql-batch-commands.html Executing SQL Statements from a Text File] | |||
<pre> | <pre> | ||
Line 66: | Line 273: | ||
</pre> | </pre> | ||
=== Create a table == | Note when I test it on raspbian, I get errors ''Access denied for user 'testuser'@'localhost' (using password: YES)''. | ||
https://dev.mysql.com/doc/refman/5.7/en/creating-tables.html | |||
It works after I follow these 2 suggestions | |||
* https://stackoverflow.com/questions/6091427/mysql-into-outfile-access-denied-but-my-user-has-all-access-and-the-fold '''GRANT FILE ON *.* TO 'asdfsdf'@'localhost';''' | |||
* https://www.raspberrypi.org/forums/viewtopic.php?p=1326803 Creating a new directory and change directory attributes '''mkdir /tmp/data; chmod 777 /tmp/data''' | |||
Following the exercise [[#How_does_importing_a_database_affect_the_current_database.3F|here]], | |||
<syntaxhighlight lang='bash'> | |||
$ mysql -u testuser -ptestpass -e \ | |||
"Use testrdb; SELECT * FROM motortrend WHERE model = 'RX4' INTO OUTFILE '/tmp/data/rx4.csv'" | |||
$ cat /tmp/data/rx4.csv | |||
Mazda RX4 21 6 160 110 3.9 2.62 16.46 0 1 4 4 Mazda RX4 | |||
newrow 21 \N \N \N \N \N \N \N \N \N \N \N RX4 | |||
</syntaxhighlight> | |||
== Create a table == | |||
Note that a database have multiple tables. | |||
* [https://www.makeuseof.com/sql-create-table/ How to Create a Table in SQL] | |||
* https://dev.mysql.com/doc/refman/5.7/en/creating-database.html | |||
* https://dev.mysql.com/doc/refman/5.7/en/creating-tables.html | |||
<syntaxhighlight lang='sql'> | <syntaxhighlight lang='sql'> | ||
mysql> CREATE DATABASE testdb; | |||
mysql> USE testdb | |||
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), | mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), | ||
-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE); | -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE); | ||
Line 83: | Line 311: | ||
death DATE | death DATE | ||
} | } | ||
</syntaxhighlight> | |||
Show all tables. | |||
<syntaxhighlight lang='sql'> | |||
mysql> SHOW TABLES; | |||
</syntaxhighlight> | </syntaxhighlight> | ||
Line 90: | Line 323: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
== Loading data into a table == | |||
https://dev.mysql.com/doc/refman/5.7/en/loading-tables.html | https://dev.mysql.com/doc/refman/5.7/en/loading-tables.html | ||
<syntaxhighlight lang='sql'> | <syntaxhighlight lang='sql'> | ||
Line 106: | Line 339: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
== Retrieve data == | |||
https://dev.mysql.com/doc/refman/5.7/en/retrieving-data.html | https://dev.mysql.com/doc/refman/5.7/en/retrieving-data.html | ||
General form | General form | ||
Line 171: | Line 404: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
== Mysql list tables and sizes - order by size == | |||
https://stackoverflow.com/questions/14569940/mysql-list-tables-and-sizes-order-by-size | https://stackoverflow.com/questions/14569940/mysql-list-tables-and-sizes-order-by-size | ||
<syntaxhighlight lang='sql'> | <syntaxhighlight lang='sql'> | ||
Line 181: | Line 414: | ||
Note: replace "schema_name" with your database name. | Note: replace "schema_name" with your database name. | ||
== Find first and last record from mysql table == | |||
https://stackoverflow.com/questions/2735395/how-to-find-first-and-last-record-from-mysql-table | https://stackoverflow.com/questions/2735395/how-to-find-first-and-last-record-from-mysql-table | ||
<syntaxhighlight lang='sql'> | <syntaxhighlight lang='sql'> | ||
# get the fields name | |||
Show columns from tablename; | |||
# To get the first record: | # To get the first record: | ||
select col1 from tab1 order by col1 asc limit 1; | select col1 from tab1 order by col1 asc limit 1; | ||
Line 189: | Line 425: | ||
# To get the last record: | # To get the last record: | ||
select col1 from tab1 order by col1 desc limit 1; | select col1 from tab1 order by col1 desc limit 1; | ||
# To get the last few records | |||
select * from tab1 order by col2 desc limit 10; | |||
</syntaxhighlight> | </syntaxhighlight> | ||
== Write/save a table/query to a text file == | |||
* http://zetcode.com/databases/mysqltutorial/exportimport/ | * http://zetcode.com/databases/mysqltutorial/exportimport/ | ||
* https://stackoverflow.com/questions/21253704/how-to-save-mysql-query-output-to-excel-or-txt-file | * https://stackoverflow.com/questions/21253704/how-to-save-mysql-query-output-to-excel-or-txt-file | ||
* https://stackoverflow.com/questions/6091427/mysql-into-outfile-access-denied-but-my-user-has-all-access-and-the-fold | * https://stackoverflow.com/questions/6091427/mysql-into-outfile-access-denied-but-my-user-has-all-access-and-the-fold | ||
< | * [https://www.mysqltutorial.org/mysql-sample-database.aspx Sample MySQL database file] <mysqlsampledatabase.sql> | ||
{{Pre}} | |||
# Method 1. mysql. Get a permission denied error | # Method 1. mysql. Get a permission denied error | ||
$ mysql -u root -p | $ mysql -u root -p | ||
Line 203: | Line 443: | ||
# https://stackoverflow.com/questions/9462416/shell-one-line-query | # https://stackoverflow.com/questions/9462416/shell-one-line-query | ||
mysql -u root -pmy_password -h ip_add --port=13306 -D DATABASENAME -e "SELECT * FROM tbl_name;" > output.txt | mysql -u root -pmy_password -h ip_add --port=13306 -D DATABASENAME -e "SELECT * FROM tbl_name;" > output.txt | ||
</ | </pre> | ||
== Create a database 'demo' and a table 'employee' == | |||
http://www.thegeekstuff.com/2011/10/mysql-tutorial-basics/ | http://www.thegeekstuff.com/2011/10/mysql-tutorial-basics/ | ||
<pre> | <pre> | ||
Line 230: | Line 459: | ||
== Access mysql using perl == | |||
http://www.cyberciti.biz/faq/how-to-access-mysql-database-using-perl | http://www.cyberciti.biz/faq/how-to-access-mysql-database-using-perl | ||
== How to See Which MySQL Tables are Taking the Most Space == | |||
https://www.howtogeekpro.com/166/how-to-see-which-mysql-tables-are-taking-the-most-space/ | https://www.howtogeekpro.com/166/how-to-see-which-mysql-tables-are-taking-the-most-space/ | ||
=== | == Check a Value in a MySQL Database from a Linux Bash Script == | ||
https://www. | [https://www.cloudsavvyit.com/1081/check-a-value-in-a-mysql-database-from-a-linux-bash-script/ Check a Value in a MySQL Database from a Linux Bash Script] | ||
== Dates and times == | |||
[https://www.muo.com/how-to-work-effectively-with-dates-and-times-in-mysql/ How to Work Effectively With Dates and Times in MySQL] | |||
== Use through R | = Administration = | ||
== Default port == | |||
3306 | |||
== phpMyAdmin == | |||
http://localhost/phpmyadmin with default username: root and the admin password you have chosen during the installation of phpMyAdmin. | |||
* [[Apache#phpMyAdmin|Apache -> phpMyAdmin]] | |||
* https://www.digitalocean.com/community/tutorials/how-to-install-and-secure-phpmyadmin-on-ubuntu-16-04 | |||
* http://tuxtweaks.com/2011/10/install-lamp-and-phpmyadmin-on-ubuntu-11-10/2/ | |||
phpMyAdmin is installed under '''/usr/share/phpmyadmin'''. | |||
Note that '''index.php''' file does not appear in '''/var/www/html''' directory. Why? A standard Apache Alias pointing every request starting with /phpmyadmin to the phpMyAdmin installation directory. See [http://paynedigital.com/articles/2011/09/setting-up-and-securing-a-phpmyadmin-install-on-ubuntu-10-04 Setting up and securing a phpMyAdmin install on Ubuntu 10.04] | |||
{{Pre}} | |||
udooer@udoo:~$ ls -l /var/www/html | |||
total 12 | |||
-rw-r--r-- 1 root root 11510 Dec 24 13:44 index.html | |||
lrwxrwxrwx 1 root root 18 Dec 24 14:00 mediawiki -> /var/lib/mediawiki | |||
udooer@udoo:~$ ls -lah /etc/apache2/ | |||
total 88K | |||
drwxr-xr-x 8 root root 4.0K Dec 24 13:44 . | |||
drwxr-xr-x 121 root root 4.0K Dec 25 08:15 .. | |||
-rw-r--r-- 1 root root 7.0K Jan 7 2014 apache2.conf | |||
drwxr-xr-x 2 root root 4.0K Dec 25 08:15 conf-available | |||
drwxr-xr-x 2 root root 4.0K Dec 25 08:15 conf-enabled | |||
-rw-r--r-- 1 root root 1.8K Jan 3 2014 envvars | |||
-rw-r--r-- 1 root root 31K Jan 3 2014 magic | |||
drwxr-xr-x 2 root root 12K Dec 24 13:45 mods-available | |||
drwxr-xr-x 2 root root 4.0K Dec 24 13:45 mods-enabled | |||
-rw-r--r-- 1 root root 320 Jan 7 2014 ports.conf | |||
drwxr-xr-x 2 root root 4.0K Dec 24 13:44 sites-available | |||
drwxr-xr-x 2 root root 4.0K Dec 24 13:44 sites-enabled | |||
udooer@udoo:~$ ls -laH /etc/apache2/sites-available/ | |||
total 20 | |||
drwxr-xr-x 2 root root 4096 Dec 24 13:44 . | |||
drwxr-xr-x 8 root root 4096 Dec 24 13:44 .. | |||
-rw-r--r-- 1 root root 1332 Jan 7 2014 000-default.conf | |||
-rw-r--r-- 1 root root 6437 Jan 7 2014 default-ssl.conf | |||
udooer@udoo:~$ ls -lah /etc/apache2/conf-enabled/ | |||
total 8.0K | |||
drwxr-xr-x 2 root root 4.0K Dec 25 08:15 . | |||
drwxr-xr-x 8 root root 4.0K Dec 24 13:44 .. | |||
lrwxrwxrwx 1 root root 30 Dec 24 13:44 charset.conf -> ../conf-available/charset.conf | |||
lrwxrwxrwx 1 root root 40 Dec 25 08:13 javascript-common.conf -> ../conf-available/javascript-common.conf | |||
lrwxrwxrwx 1 root root 44 Dec 24 13:44 localized-error-pages.conf -> ../conf-available/localized-error-pages.conf | |||
lrwxrwxrwx 1 root root 46 Dec 24 13:44 other-vhosts-access-log.conf -> ../conf-available/other-vhosts-access-log.conf | |||
lrwxrwxrwx 1 root root 33 Dec 25 08:15 phpmyadmin.conf -> ../conf-available/phpmyadmin.conf | |||
lrwxrwxrwx 1 root root 31 Dec 24 13:44 security.conf -> ../conf-available/security.conf | |||
lrwxrwxrwx 1 root root 36 Dec 24 13:44 serve-cgi-bin.conf -> ../conf-available/serve-cgi-bin.conf | |||
udooer@udoo:~$ cat /etc/apache2/conf-available/phpmyadmin.conf | |||
# phpMyAdmin default Apache configuration | |||
Alias /phpmyadmin /usr/share/phpmyadmin | |||
<Directory /usr/share/phpmyadmin> | |||
Options FollowSymLinks | |||
DirectoryIndex index.php | |||
<IfModule mod_php5.c> | |||
AddType application/x-httpd-php .php | |||
php_flag magic_quotes_gpc Off | |||
php_flag track_vars On | |||
php_flag register_globals Off | |||
php_admin_flag allow_url_fopen Off | |||
php_value include_path . | |||
php_admin_value upload_tmp_dir /var/lib/phpmyadmin/tmp | |||
php_admin_value open_basedir /usr/share/phpmyadmin/:/etc/phpmyadmin/:/var/lib/phpmyadmin/:/usr/share/php/php-gettext/:/usr/share/javascript/ | |||
</IfModule> | |||
</Directory> | |||
</pre> | |||
If we want to change the URL or the port number of phpMyAdmin, follow [https://askubuntu.com/questions/969417/change-phpmyadmin-url-through-etc-apache2-conf-available-phpmyadmin-conf this]. | |||
== Configuration files == | |||
[https://severalnines.com/blog/mysql-docker-containers-understanding-basics MySQL Docker Containers: Understanding the basics] | |||
On Ubuntu 18.04 | |||
<pre> | |||
$ sudo apt update | |||
$ sudo apt install mariadb-server mariadb-client | |||
$ sudo systemctl status mariadb | |||
$ sudo mysql_secure_installation | |||
$ mysql –u root -p | |||
mysql Ver 15.1 Distrib 10.1.41-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 | |||
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. | |||
Usage: mysql [OPTIONS] [database] | |||
Default options are read from the following files in the given order: | |||
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf | |||
... | |||
</pre> | |||
[https://websiteforstudents.com/installing-mariadb-database-server-on-ubuntu-18-04-lts-beta-server/ Ubuntu 18.04 configuration file] location '''/etc/mysql/mariadb.conf.d/50-server.cnf''' | |||
This can be found by using '''mysql --help''' or '''mysqld --help --verbose'''. On Ubuntu 16.04, it shows | |||
<syntaxhighlight lang='bash'> | |||
$ mysql --help | |||
... | |||
Default options are read from the following files in the given order: | |||
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf | |||
... | |||
$ tree /etc/mysql/ | |||
/etc/mysql/ | |||
├── conf.d | |||
│ ├── mysql.cnf | |||
│ └── mysqldump.cnf | |||
├── debian.cnf | |||
├── debian-start | |||
├── my.cnf -> /etc/alternatives/my.cnf | |||
├── my.cnf.fallback | |||
├── mysql.cnf | |||
└── mysql.conf.d | |||
├── mysqld.cnf | |||
└── mysqld_safe_syslog.cnf | |||
2 directories, 9 files | |||
</syntaxhighlight> | |||
== Errors == | |||
* Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (38) | |||
* Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (on Debian 10) when I run '''sudo mysql_secure_installation''' after I installed mariadb-server. | |||
Solution: Make sure to run the following after installing mariadb-server | |||
<pre> | |||
sudo systemctl start mariadb.service | |||
sudo systemctl enable mariadb.service | |||
</pre> | |||
== Default database location == | |||
On Debian/Ubuntu, it is '''/var/lib/mysql'''. | |||
<syntaxhighlight lang='bash'> | |||
$ mysql -uroot -p -e 'SHOW VARIABLES WHERE Variable_Name = "datadir"' | |||
</syntaxhighlight> | |||
OR | |||
<syntaxhighlight lang='mysql'> | |||
mysql> select @@datadir; | |||
</syntaxhighlight> | |||
== Change data location == | |||
https://stackoverflow.com/questions/1795176/how-to-change-mysql-data-directory | |||
# sudo /etc/init.d/mysql stop | |||
# sudo cp -R -p /var/lib/mysql /newpath | |||
# sudo gedit /etc/mysql/my.cnf | |||
# ''Look for the entry for datadir, and change the path (which should be /var/lib/mysql) to the new data directory.'' | |||
# sudo gedit /etc/apparmor.d/usr.sbin.mysqld | |||
# ''Look for lines beginning with /var/lib/mysql. Change /var/lib/mysql in the lines with the new path.'' | |||
# sudo /etc/init.d/apparmor reload | |||
# sudo /etc/init.d/mysql restart | |||
== Full Backup and Restore with Mariabackup == | |||
* The '''mariabackup''' command is a '''physical backup''' method (cf SQL command is a '''logical backup''' method) | |||
** A physical backup involves copying the data files directly. It is faster and more efficient, but less portable. These data files are specific to the hardware, operating system, and version of MariaDB that you are using. | |||
** A logical backup exports the data in a human-readable format such as SQL, while a physical backup copies the raw data files directly. | |||
* [https://mariadb.com/kb/en/full-backup-and-restore-with-mariabackup/ MariaDB Backup] or [https://mariadb.com/docs/server/data-operations/backups/community-server/mariadb-backup/ here] | |||
* You can restore the backup using the '''rsync''' command (as long as the MariaDB Server process is stopped on the target server, you can technically restore the backup using any file copying tool, such as cp or rysnc). ''sudo systemctl restart mysqld'' | |||
<pre> | |||
$ mariabackup --backup \ | |||
--target-dir=/var/mariadb/backup/ \ | |||
--user=mariabackup --password=mypassword | |||
</pre> | |||
<pre> | |||
$ mariabackup --prepare \ | |||
--target-dir=/var/mariadb/backup/ | |||
$ mariabackup --copy-back \ | |||
--target-dir=/var/mariadb/backup/ | |||
</pre> | |||
== How to backup/export and load/import a single database and/or table from a MySQL database == | |||
* https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html | |||
* https://www.howtogeekpro.com/157/how-to-backup-export-a-single-table-from-a-mysql-database/ | |||
* https://stackoverflow.com/questions/4546778/how-can-i-import-a-database-with-mysql-from-terminal | |||
* [https://www.howtogeekpro.com/160/how-to-exclude-tables-from-mysql-exports-using-mysqldump/ How to Exclude Tables from MySQL Exports Using mysqldump] | |||
<syntaxhighlight lang='bash'> | |||
# Export | |||
# Method 1: directly | |||
$ mysqldump -u USERNAME --password="PW" DB_NAME > backup.sql | |||
# Method 2: gzip | |||
$ mysqldump -u USERNAME --password="PW" DB_NAME | gzip --best --verbose > backup.sql.gz | |||
# Multiple database | |||
$ mysqldump -u USERNAME --password="PW" --all-databases > all_databases.sql | |||
# Import | |||
$ mysql -u UserName -p Password -e 'Create Database DB_NAME' | |||
# Method 1 (the database needs to be created first) | |||
$ mysql -u UserName -p Password -h Hostname DB_NAME < backup.sql | |||
# Method 2 (not working?) | |||
$ mysqlimport -u UserName -p Password backup.sql | |||
# Multiple database (no need to create an empty database) | |||
$ mysql -u UserName -p < backup.sql | |||
</syntaxhighlight> | |||
Note that | |||
# the back up file is a text file. | |||
# it seems it is common to use '''sql''' as the extension name | |||
# the user information will not be retained in the backup file. | |||
# the original database name is saved in the backup file | |||
# the database name ''DB_NAME'' in importing is required. It the database name does not to be the same as the original if only one database was dumped. However the database ''DB_NAME'' has to be created beforehand (mysql> CREATE DATABASE DB_NAME2). If the database has not existed or we omit the DB_NAME in importing, we will get an error | |||
<pre> | |||
ERROR 1046 (3D000) at line 22: No database selected | |||
# or | |||
ERROR 1049 (42000): Unknown database 'testdb' | |||
</pre> | |||
== Export a CSV file == | |||
[https://www.cloudsavvyit.com/621/how-to-export-a-csv-file-from-mysql-command-line/ How to export a CSV file from MySQL command line] | |||
== Move the MySQL data directory == | |||
* https://askubuntu.com/questions/137424/how-do-i-move-the-mysql-data-directory | |||
* [https://www.digitalocean.com/community/tutorials/how-to-move-a-mysql-data-directory-to-a-new-location-on-ubuntu-16-04 How To Move a MySQL Data Directory to a New Location on Ubuntu 16.04] | |||
== SSL == | |||
[https://www.cyberciti.biz/faq/how-to-setup-mariadb-ssl-and-secure-connections-from-clients/ How to set up MariaDB SSL and secure connections from clients] | |||
== Secure Your Database == | |||
[https://www.cloudsavvyit.com/3751/how-should-you-secure-your-database/ How Should You Secure Your Database?] | |||
== Injection attack == | |||
[https://en.wikipedia.org/wiki/SQL_injection SQL injection] | |||
== How to Analyse MySQL Performance == | |||
[https://www.cloudsavvyit.com/10275/how-to-analyse-mysql-performance-problems/ How to Analyse MySQL Performance Problems] | |||
== Load balance == | |||
[https://www.howtoforge.com/how-to-set-up-load-balancing-mysql-server-with-proxysql-on-debian-11/ How to Install a Load Balancing MySQL Server with ProxySQL on Debian 11] | |||
= Office = | |||
== LibreOffice == | |||
[https://youtu.be/lhRJFgDG-5o Introduction to Databases: LibreOffice Base Tutorial] (youtube) | |||
= Use through R = | |||
Examples from r-bloggers | Examples from r-bloggers | ||
* http://www.r-bloggers.com/mysql-and-r/ | * http://www.r-bloggers.com/mysql-and-r/ | ||
Line 247: | Line 721: | ||
* [http://www.stat.berkeley.edu/~spector/sql.pdf Phil Spector] | * [http://www.stat.berkeley.edu/~spector/sql.pdf Phil Spector] | ||
== Installation == | |||
* http://cran.r-project.org/web/packages/RMySQL/ | * http://cran.r-project.org/web/packages/RMySQL/ | ||
* https://db.rstudio.com/my-sql/ (more instructions) | * https://db.rstudio.com/my-sql/ (more instructions) | ||
=== Ubuntu/Debian === | |||
First, log in using root. | First, log in using root. | ||
<pre> | <pre> | ||
Line 258: | Line 732: | ||
Go to shell and | Go to shell and | ||
<syntaxhighlight lang='rsplus'> | <syntaxhighlight lang='rsplus'> | ||
sudo apt-get install libdbd-mysql (not | # sudo apt-get install libdbd-mysql (this line seems not necessary) | ||
sudo apt-get install libmysqlclient-dev | # sudo apt-get install libmysqlclient-dev (Ubuntu 14) | ||
sudo apt-get install -y libmariadb-dev (Debian Stretch) | |||
# R | # R | ||
# install.packages("RMySQL") | # install.packages("RMySQL") | ||
</syntaxhighlight> | </syntaxhighlight> | ||
=== Windows === | |||
Check my note with complete screenshots at [[File:Install MySQL on Windows.pdf]]. | Check my note with complete screenshots at [[File:Install MySQL on Windows.pdf]]. | ||
== Open and close connection: dbConnect/dbDisconnect == | |||
<syntaxhighlight lang='rsplus'> | <syntaxhighlight lang='rsplus'> | ||
con <- dbConnect(MySQL(), user="me", password="nuts2u", dbname="my_db", host="localhost") | con <- dbConnect(MySQL(), user="me", password="nuts2u", dbname="my_db", host="localhost") | ||
Line 276: | Line 751: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
== List tables and fields: dbListTables and dbListFields == | |||
<pre> | <pre> | ||
dbListTables(mydb) | dbListTables(mydb) | ||
Line 282: | Line 757: | ||
</pre> | </pre> | ||
== Read and write entire tables: dbReadTable and dbWriteTable == | |||
We can create tables in the database using R dataframes. | We can create tables in the database using R dataframes. | ||
<pre> | <pre> | ||
Line 289: | Line 764: | ||
</pre> | </pre> | ||
== Query: dbGetQuery and dbSendQuery == | |||
You can process query results row by row, in blocks or all at once. The highly useful function '''dbGetQuery(con, sql)''' returns all query results as a data frame. With '''dbSendQuery''', you can get all or partial results with fetch. | You can process query results row by row, in blocks or all at once. The highly useful function '''dbGetQuery(con, sql)''' returns all query results as a data frame. With '''dbSendQuery''', you can get all or partial results with fetch. | ||
<pre> | <pre> | ||
Line 300: | Line 775: | ||
</pre> | </pre> | ||
== Aggregate and Sort == | |||
<pre> | <pre> | ||
df = dbGetQuery(con, "SELECT mfg, avg(hp) AS meanHP FROM motortrend GROUP BY mfg ORDER BY meanHP DESC") | df = dbGetQuery(con, "SELECT mfg, avg(hp) AS meanHP FROM motortrend GROUP BY mfg ORDER BY meanHP DESC") | ||
Line 306: | Line 781: | ||
</pre> | </pre> | ||
== dbApply() == | |||
<pre> | <pre> | ||
Line 314: | Line 789: | ||
</pre> | </pre> | ||
== Retrieving AUTO_INCREMENT IDs == | |||
* http://www.w3schools.com/sql/sql_autoincrement.asp | * http://www.w3schools.com/sql/sql_autoincrement.asp | ||
<pre> | <pre> | ||
Line 337: | Line 812: | ||
</pre> | </pre> | ||
== Full example from Jeffrey Breen == | |||
http://www.r-bloggers.com/slides-%E2%80%9Caccessing-databases-from-r%E2%80%9D-rstats/ | http://www.r-bloggers.com/slides-%E2%80%9Caccessing-databases-from-r%E2%80%9D-rstats/ | ||
First, create new database & user in MySQL: | First, create new database & user in MySQL: | ||
< | <syntaxhighlight lang='mysql'> | ||
mysql> create database testrdb; | mysql> create database testrdb; | ||
mysql> grant all privileges on testrdb.* to 'testuser'@'localhost' identified by 'testpass'; | mysql> grant all privileges on testrdb.* to 'testuser'@'localhost' identified by 'testpass'; | ||
mysql> flush privileges; | mysql> flush privileges; | ||
</ | </syntaxhighlight> | ||
In R, load the “mtcars” data.frame, clean it up, and write it to a new “motortrend” table: | In R, load the “mtcars” data.frame, clean it up, and write it to a new “motortrend” table: | ||
< | <syntaxhighlight lang='rsplus'> | ||
library(stringr) | library(stringr) | ||
library(RMySQL) | library(RMySQL) | ||
Line 357: | Line 832: | ||
mtcars$mfg[mtcars$mfg=='Merc'] = 'Mercedes' | mtcars$mfg[mtcars$mfg=='Merc'] = 'Mercedes' | ||
mtcars$model = str_split_fixed(rownames(mtcars), ' ', 2)[,2] | mtcars$model = str_split_fixed(rownames(mtcars), ' ', 2)[,2] | ||
> dim(mtcars) | |||
[1] 32 13 | |||
> mtcars[1:3,] | |||
mpg cyl disp hp drat wt qsec vs am gear carb mfg model | |||
Mazda RX4 21.0 6 160 110 3.90 2.620 16.46 0 1 4 4 Mazda RX4 | |||
Mazda RX4 Wag 21.0 6 160 110 3.90 2.875 17.02 0 1 4 4 Mazda RX4 Wag | |||
Datsun 710 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1 Datsun 710 | |||
# connect to local MySQL database (host='localhost' by default) | # connect to local MySQL database (host='localhost' by default) | ||
con = dbConnect( | con = dbConnect(MySQL(), "testrdb", username="testuser", password="testpass") | ||
dbWriteTable(con, 'motortrend', mtcars) | dbWriteTable(con, 'motortrend', mtcars) | ||
dbDisconnect(con) | dbDisconnect(con) | ||
</ | </syntaxhighlight> | ||
And check the database from mysql | And check the database from mysql | ||
< | <syntaxhighlight lang='bash'> | ||
mli@PhenomIIx6:~$ mysql -u testuser -p | mli@PhenomIIx6:~$ mysql -u testuser -p | ||
Enter password: | Enter password: | ||
Line 442: | Line 925: | ||
mysql> | mysql> | ||
</ | </syntaxhighlight> | ||
== Setting Up Raspberry Pi Temperature/Humidity Sensors for Data Analysis in R == | |||
[https://roh.engineering/post/setting-up-raspberry-pi-temperature-humidity-sensors-for-data-analysis-in-r/ Setting Up Raspberry Pi Temperature/Humidity Sensors for Data Analysis in R] | |||
== How does importing a database affect the current database? == | |||
Continue from the database (testrdb) from the previous section. | |||
<syntaxhighlight lang='bash'> | |||
$ mysql -u testuser -ptestpass -e "use testrdb; select count(*) from motortrend" | |||
+----------+ | |||
| count(*) | | |||
+----------+ | |||
| 32 | | |||
+----------+ | |||
$ mysql -u testuser -ptestpass -e "use testrdb; select * FROM motortrend WHERE model = 'RX4'" | |||
+-----------+------+------+------+------+------+------+-------+------+------+------+------+-------+-------+ | |||
| row_names | mpg | cyl | disp | hp | drat | wt | qsec | vs | am | gear | carb | mfg | model | | |||
+-----------+------+------+------+------+------+------+-------+------+------+------+------+-------+-------+ | |||
| Mazda RX4 | 21 | 6 | 160 | 110 | 3.9 | 2.62 | 16.46 | 0 | 1 | 4 | 4 | Mazda | RX4 | | |||
+-----------+------+------+------+------+------+------+-------+------+------+------+------+-------+-------+ | |||
# Modify a record | |||
$ mysql -u testuser -ptestpass -e "use testrdb; update motortrend SET mpg=22 WHERE model = 'RX4'" | |||
$ mysql -u testuser -ptestpass -e "use testrdb; update motortrend SET mfg='toyota' WHERE model = 'RX4'" | |||
# Remove a record. Use 'Limit' if necessary | |||
# https://stackoverflow.com/questions/18378190/how-to-delete-a-certain-row-from-mysql-table-with-same-column-values | |||
$ mysql -u testuser -ptestpass -e "use testrdb; DELETE FROM motortrend WHERE model = 'Corona'"; | |||
# Insert a record | |||
$ mysql -u testuser -ptestpass -e \ | |||
"use testrdb; INSERT INTO motortrend VALUES ('newrow',21, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'RX4')"; | |||
$ mysql -u testuser -ptestpass -e "use testrdb; select * FROM motortrend WHERE model = 'RX4'" | |||
+-----------+------+------+------+------+------+------+-------+------+------+------+------+--------+-------+ | |||
| row_names | mpg | cyl | disp | hp | drat | wt | qsec | vs | am | gear | carb | mfg | model | | |||
+-----------+------+------+------+------+------+------+-------+------+------+------+------+--------+-------+ | |||
| Mazda RX4 | 22 | 6 | 160 | 110 | 3.9 | 2.62 | 16.46 | 0 | 1 | 4 | 4 | toyota | RX4 | | |||
| newrow | 21 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | RX4 | | |||
+-----------+------+------+------+------+------+------+-------+------+------+------+------+--------+-------+ | |||
# Import. | |||
$ mysql -u testuser -ptestpass testrdb < testrdb.sql | |||
$ mysql -u testuser -ptestpass -e "use testrdb; select * FROM motortrend WHERE model = 'RX4'" | |||
+-----------+------+------+------+------+------+------+-------+------+------+------+------+-------+-------+ | |||
| row_names | mpg | cyl | disp | hp | drat | wt | qsec | vs | am | gear | carb | mfg | model | | |||
+-----------+------+------+------+------+------+------+-------+------+------+------+------+-------+-------+ | |||
| Mazda RX4 | 21 | 6 | 160 | 110 | 3.9 | 2.62 | 16.46 | 0 | 1 | 4 | 4 | Mazda | RX4 | | |||
+-----------+------+------+------+------+------+------+-------+------+------+------+------+-------+-------+ | |||
$ mysql -u testuser -ptestpass -e "use testrdb; select count(*) from motortrend" | |||
+----------+ | |||
| count(*) | | |||
+----------+ | |||
| 32 | | |||
+----------+ | |||
</syntaxhighlight> | |||
= [https://db.rstudio.com/dplyr/ dbplyr] = | |||
[https://emilyriederer.netlify.app/post/sql-generation/ Generating SQL with {dbplyr} and sqlfluff] | |||
= RODBC = | |||
= SQLite = | |||
* [https://www.digitalocean.com/community/tutorials/how-and-when-to-use-sqlite How and When to Use Sqlite] | |||
* [https://cran.r-project.org/web/packages/RSQLite/index.html RSQLite] package | |||
* [https://cran.r-project.org/web/packages/readwritesqlite/index.html readwritesqlite]: Enhanced Reading and Writing for 'SQLite' Databases | |||
* [https://docs.pi-hole.net/database/ pi-hole] is using SQLite3 | |||
* [https://www.howtoforge.com/how-to-install-sqlite-and-sqlite-browser-on-ubuntu-2004/ How to Install SQLite and SQLite Browser on Ubuntu 20.04] | |||
* [https://www.cloudsavvyit.com/13161/what-is-sqlite-and-why-is-it-so-popular/ What Is SQLite and Why Is It So Popular?] | |||
== How to Use DB Browser for SQLite == | |||
[https://www.howtogeek.com/704243/how-to-use-db-browser-for-sqlite-on-linux/ How to Use DB Browser for SQLite on Linux] | |||
= PostgreSQL = | |||
* [http://www.thegeekstuff.com/2017/02/postgresql-basics/ 8 PostgreSQL Examples to Install, Create DB & Table, Insert & Select Records] | |||
* [https://opensource.com/article/17/10/set-postgres-database-your-raspberry-pi How to set up a Postgres database on a Raspberry Pi] | |||
* [http://freerangestats.info/blog/2018/07/07/twitter-monitor Setting up RStudio Server, Shiny Server and PostgreSQL] | |||
== pgcli == | |||
[https://www.pgcli.com/ Pgcli]: a command line interface for Postgres with auto-completion and syntax highlighting. | |||
== Docker server + R client == | |||
[https://colinfay.me/clients-db/ clientsdb - A docker image with clients comments], [https://github.com/ColinFay/clientsdb github], [https://hub.docker.com/r/colinfay/clientsdb docker] | |||
This requires | |||
* Installation of some library: "sudo apt install libpq-dev" (PostgreSQL library in Ubuntu) | |||
* R's packages "DBI" and "RPostgres" | |||
= CouchDB = | |||
[https://www.howtoforge.com/tutorial/ubuntu-couchdb/ How to Install Apache CouchDB on Ubuntu 18.04 LTS] | |||
== [https:// | = [https://www.influxdata.com/ InFluxDB] = | ||
* Used in [http://www.linuxuk.org/post/monitoring-plants-with-ubuntu/ Monitoring a Greenhouse with Ubuntu] | |||
* [https://grafana.com/ Grafana] | |||
* [https://www.codementor.io/ashish1dev/influxdb-to-grafana-visualizing-time-series-data-in-real-time-5hxhaq0uj InfluxDB To Grafana: Visualizing Time Series Data in Real Time] | |||
* [https://www.instructables.com/id/Bird-Feeder-Monitor-V20/ Bird Feeder Monitor V2.0] | |||
* [https://snapcraft.io/gravit-designer Gravit designer] snap app | |||
== | = [https://www.mongodb.com/ MongoDB] = | ||
* [https://www.makeuseof.com/sql-vs-nosql-whats-the-best-database-for-your-next-project/ SQL vs. NoSQL: What's the Best Database for Your Next Project?]. NoSQL's schemaless structure allows you to insert additional arbitrary data into documents. | |||
* [https://itsfoss.com/install-mongodb-ubuntu/ How to Install MongoDB on Ubuntu] | |||
* [https://colinfay.me/hexmake-shiny-contest/ {hexmake} is one of the 5 Grand Prizes of the 2020 Shiny Contest] | |||
* [https://www.cloudsavvyit.com/8866/how-to-backup-and-migrate-a-mongodb-database/ How To Backup and Migrate a MongoDB Database] | |||
* [https://www.lynda.com/MongoDB-tutorials/Using-find-query-documents/2835008/2365038-4.html Learning MongoDB] from Lynda.com | |||
* [https://cran.r-project.org/web/packages/mongolite/index.html mongolite]: Fast and Simple 'MongoDB' Client for R | |||
* [https://hub.docker.com/_/mongo/ mongo] Docker Official Images | |||
== | = DuckDB = | ||
[https://www.r-bloggers.com/2024/06/joining-the-flock-from-r-working-with-data-on-motherduck/ Joining the flock from R: working with data on MotherDuck] | |||
== | = Redis = | ||
[ | * [[Redis|Redis]] | ||
* [https://medium.com/swlh/redis-vs-mongodb-b29703c89dce Redis VS MongoDB] | |||
= Find database online = | |||
* http://www.inside-r.org/howto/finding-data-internet | * http://www.inside-r.org/howto/finding-data-internet | ||
* http://www.r-bloggers.com/list-of-public-databases-from-the-washington-post/ | * http://www.r-bloggers.com/list-of-public-databases-from-the-washington-post/ | ||
= Install [http://hbase.apache.org/ Apache HBase] = | |||
Follow the [http://hbase.apache.org/book/quickstart.html Quick Start] to downloaded hbase tar ball. Suppose we save the tar ball under ~/Downloads folder and extract it in the same directory. We shall edit '''conf/hbase-site.xml''' file according to their instruction. The following is my case. | |||
<pre> | |||
$ tar xzvf hbase-0.98.3-hadoop2-bin.tar.gz | |||
$ cd hbase-0.98.3-hadoop2/ | |||
$ cat conf/hbase-site.xml | |||
<?xml version="1.0"?> | |||
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?> | |||
<configuration> | |||
<property> | |||
<name>hbase.rootdir</name> | |||
<value>file:///home/brb/Downloads/hbase-0.98.3-hadoop2/hbase</value> | |||
</property> | |||
<property> | |||
<name>hbase.zookeeper.property.dataDir</name> | |||
<value>/home/brb/Downloads/hbase-0.98.3-hadoop2/zookeeper</value> | |||
</property> | |||
</configuration> | |||
</pre> | |||
Before we follow the getting started guide to launch HBase, we shall make sure JAVA_HOME environment variable is created. | |||
<pre> | |||
$ ls /usr/lib/java | |||
$ export JAVA_HOME=/usr/lib/jvm/java-6-openjdk-amd64 | |||
</pre> | |||
Note that the last line may be replaced by | |||
<pre> | |||
export JAVA_HOME=$(readlink -f /usr/bin/javac | sed "s:bin/javac::") | |||
</pre> | |||
Then we can launch HBase, | |||
<pre style="white-space: pre-wrap; | |||
white-space: -moz-pre-wrap; | |||
white-space: -pre-wrap; | |||
white-space: -o-pre-wrap; | |||
word-wrap: break-word;"> | |||
$ ./bin/start-hbase.sh | |||
starting master, logging to /home/brb/Downloads/hbase-0.98.3-hadoop2/bin/../logs/hbase-brb-master-brb-P45T-A.out | |||
brb@brb-P45T-A:~/Downloads/hbase-0.98.3-hadoop2$ ./bin/hbase shell | |||
2014-07-06 09:51:34,621 INFO [main] Configuration.deprecation: hadoop.native.lib is deprecated. Instead, use io.native.lib.available | |||
HBase Shell; enter 'help<RETURN>' for list of supported commands. | |||
Type "exit<RETURN>" to leave the HBase Shell | |||
Version 0.98.3-hadoop2, rd5e65a9144e315bb0a964e7730871af32f5018d5, Sat May 31 19:56:09 PDT 2014 | |||
hbase(main):001:0> create 'test', 'cf' | |||
2014-07-06 09:51:49,510 WARN [main] util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable | |||
0 row(s) in 2.0770 seconds | |||
=> Hbase::Table - test | |||
hbase(main):002:0> list 'test' | |||
TABLE | |||
test | |||
1 row(s) in 0.0530 seconds | |||
=> ["test"] | |||
hbase(main):003:0> exit | |||
brb@brb-P45T-A:~/Downloads/hbase-0.98.3-hadoop2$ ./bin/hbase | |||
shell2014-07-06 09:53:37,480 INFO [main] Configuration.deprecation: hadoop.native.lib is deprecated. Instead, use io.native.lib.available | |||
HBase Shell; enter 'help<RETURN>' for list of supported commands. | |||
Type "exit<RETURN>" to leave the HBase Shell | |||
Version 0.98.3-hadoop2, rd5e65a9144e315bb0a964e7730871af32f5018d5, Sat May 31 19:56:09 PDT 2014 | |||
hbase(main):001:0> list 'test' | |||
TABLE | |||
2014-07-06 09:53:44,373 WARN [main] util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable | |||
test | |||
1 row(s) in 1.4800 seconds | |||
=> ["test"] | |||
hbase(main):002:0> put 'test', 'row1', 'cf:a', 'value1' | |||
0 row(s) in 0.4460 seconds | |||
hbase(main):003:0> put 'test', 'row2', 'cf:b', 'value2' | |||
0 row(s) in 0.0140 seconds | |||
hbase(main):004:0> put 'test', 'row3', 'cf:c', 'value3' | |||
0 row(s) in 0.0050 seconds | |||
hbase(main):005:0> scan 'test' | |||
ROW COLUMN+CELL | |||
row1 column=cf:a, timestamp=1404654837532, value=value1 | |||
row2 column=cf:b, timestamp=1404654856976, value=value2 | |||
row3 column=cf:c, timestamp=1404654866298, value=value3 | |||
3 row(s) in 0.0560 seconds | |||
hbase(main):006:0> get 'test', 'row1' | |||
COLUMN CELL | |||
cf:a timestamp=1404654837532, value=value1 | |||
1 row(s) in 0.0280 seconds | |||
hbase(main):007:0> disable 'test' | |||
0 row(s) in 1.6050 seconds | |||
hbase(main):008:0> drop 'test' | |||
0 row(s) in 0.2290 seconds | |||
hbase(main):009:0> exit | |||
brb@brb-P45T-A:~/Downloads/hbase-0.98.3-hadoop2$ | |||
</pre> |
Latest revision as of 09:28, 20 August 2024
Installation issues
Fail to start
On Ubuntu 12.04, we can use
sudo dpkg-reconfigure mysql-server-5.5
InnoDB vs MyISAM
The default is InnoDB.
Under Shell
Manual/Documentation
https://dev.mysql.com/doc/refman
MariaDB vs MySQL
- MariaDB vs MySQL: Key Performance Differences
- MariaDB vs MySQL – Key Differences – Comparing MySQL 8.0 with MariaDB 10.5
- MariaDB versus MySQL: Compatibility
Check server version
Check mySQL and MariaDB Server Version
Installation, setup root password, remove the need of 'sudo'
- How to install MySQL 8 on Debian 10. It includes how to secure it and how to back up.
- Installing MariaDB Database Server On Ubuntu 18.04 LTS Server
- How to Install MariaDB on Ubuntu 18.04
- How to Install Wallabag on Ubuntu 18.04 LTS
- How to install MySQL server on Ubuntu 22.04 LTS Linux
- The Perfect Server - Ubuntu 18.04 (Bionic Beaver) with Apache, PHP, MySQL, PureFTPD, BIND, Postfix, Dovecot and ISPConfig 3.1. Edit /etc/mysql/debian.cnf file and set the root password there in addition to setting the root password by mysql_secure_installation.
- How to create MySQL admin user (superuser) account MySQL 8.x or MariaDB 10.4+
Debian 9 (Stretch) package now ships with the UNIX_SOCKET authentication plugin enabled and you are no longer asked to set a root password when installing the package. Even when setting a root password via the mysql_secure_installation script you are still denied. So we need to run 'update'; see the command below.
See a solution at Debian 9 “Stretch” and MySQL/MariaDB root password.
$ sudo apt-get install mysql-server $ sudo systemctl status mariadb $ sudo systemctl start mariadb.service $ sudo systemctl enable mariadb.service $ sudo mysql_secure_installation # type the new root password for your MariaDB server and # type 'Y' for all configurations Enter current password for root (enter for none): Set a root password? [y/n] y Remove anonymous users? [y/n] y Disallow root login remotely? [y/n] y Remove test database and access to it? [y/n] y Reload privilege tables now? [y/n] y $ sudo mysql -u root mysql -e "update user set plugin='' where user='root'; flush privileges;" ## With the above command, I don't need 'sudo' in the next command $ mysql -u root -p ... Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 12 Server version: 10.1.23-MariaDB-9+deb9u1 Raspbian 9.0 ... MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.02 sec) MariaDB [(none)]> exit Bye
Access denied for user 'root'@'localhost'
- https://superuser.com/questions/603026/mysql-how-to-fix-access-denied-for-user-rootlocalhost
- Tutorial – After Installing MySQL on Ubutnu 18.04, Fix “Access denied for user ‘root’@’localhost'” Error
sudo service mysql stop sudo mkdir -p /var/run/mysqldsudo chown mysql:mysql /var/run/mysqld sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking & mysql -u root mysql> FLUSH PRIVILEGES; mysql> USE mysql; mysql> UPDATE user SET authentication_string=PASSWORD("MyPASSWORDHERE") WHERE User='root'; mysql> UPDATE user SET plugin="mysql_native_password" WHERE User='root'; mysql> quit sudo pkill mysqld sudo service mysql start
MyPASSWORDHERE can be as short as 2 characters.
Reset root password
https://www.digitalocean.com/community/tutorials/how-to-reset-your-mysql-or-mariadb-root-password
Installing MariaDB Binary Tarballs
- https://mariadb.com/kb/en/library/installing-mariadb-binary-tarballs/
- Installing MariaDB on Ubuntu 18.04 from the MariaDB Repositories
Authentication
- MySQL 8.0. "Authentication plugin 'caching_sha2_password'
- Fixing "Authentication plugin 'caching_sha2_password' cannot be loaded" errors - revert to the mysql_native_password mechanism.
- Upgrading to MySQL 8.0 : Default Authentication Plugin Considerations
- Authentication Plugin - SHA-256
Uninstall
sudo service mysql stop sudo apt-get --purge remove "mysql*" sudo mv /etc/mysql/ /tmp/mysql_configs/ sudo reboot
How do I turn off the mysql password validation?
Normally a password as short as 3 characters is enough for the root user.
Create a new local user account to grant access to a database
http://www.cyberciti.biz/faq/mysql-user-creation/
NOTE: please be mindful when creating a user name. There is no protection against creating an old user's name. If you accidentally create a same user name as say mediawiki DB user, then mediawiki will not be working.
$ mysql -u root -p OR $ mysql -u root -h myserver-sever.com -p
Create a new mysql database called demo;
mysql> CREATE DATABASE testdb;
Create a new user called user1 for database demo
mysql> GRANT ALL ON testdb.* TO user1@localhost IDENTIFIED BY 'mypassword';
If we quit mysql and log in again using the new account, the 'SHOW DATABASES' will only show databases that the new account can access
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | testdb | +--------------------+ 2 rows in set (0.00 sec)
User user1 can connect to mysql server demo database using following command:
$ mysql -u user1 -p testdb OR $ mysql -u user1 -h mysql.server.com -p testdb
Create Remote MySQL user and grant remote access to databases
How to Allow MySQL remote connections in Ubuntu Server 18.04
Show all users
SELECT User, Host, Password FROM mysql.user; SELECT DISTINCT User FROM mysql.user;
Show the privileges of a user
mysql> SHOW GRANTS FOR 'user1'@'localhost'; +--------------------------------------------------------------------------------------------------------------+ | Grants for user1@localhost | +--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF' | | GRANT ALL PRIVILEGES ON `testdb`.* TO 'user1'@'localhost' | +--------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
Another way is through phpMyAdmin. Click on 'Databases' and in the Action column click 'Check Privileges' in the corresponding row of the desired database.
Remove an account
DROP USER 'jeffrey'@'localhost';
Remove a database
mysql> DROP DATABASE db_name; mysql> SHOW DATABASES;
Show the current user
mysql> SELECT USER();
Show the current database
mysql> SELECT DATABASE();
Note on MariaDB it shows the current database.
MariaDB [(none)]> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [mysql]>
How do you deselect MySQL database?
https://stackoverflow.com/questions/27895328/how-do-you-deselect-mysql-database. Ans: You just select another database and USE it.
Version vs Distrib number of MySQL
https://stackoverflow.com/questions/8645184/version-vs-distrib-number-of-mysql
- Ver refers to the version of the mysql command line client - what you are envoking by typing 'mysql'
- Distrib refers to the mysql server version your client was built with. This is not to be confused with the mysql server you are connected to, which can be obtained with SELECT VERSION();
On Ubuntu 14.04
$ mysql --version mysql Ver 14.14 Distrib 5.5.58, for debian-linux-gnu (x86_64) using readline 6.3 $ mysql -u root -p -e 'select version()' Enter password: +-------------------------+ | version() | +-------------------------+ | 5.5.58-0ubuntu0.14.04.1 | +-------------------------+
On Docker's MySQL
# mysql --version mysql Ver 14.14 Distrib 5.7.20, for Linux (x86_64) using EditLine wrapper # mysql --user=root --password=$MYSQL_ROOT_PASSWORD -e 'select version()' mysql: [Warning] Using a password on the command line interface can be insecure. +-----------+ | version() | +-----------+ | 5.7.20 | +-----------+
MYSQL commands
Find out about your MySQL version and all installed plugins:
mysql> select version(); mysql> show plugins;
Executing SQL Statements from a Text File
$ mysql db_name $ mysql -h host -u user -p db_name # db_name is not a password ! $ mysql -h host -u user -pPASSWORD db_name # no space after "-p" $ mysql db_name < text_file # text_file that contains the statements you wish to execute/Batch mode $ mysql db_name < text_file > output.txt mysql> source file_name
Note when I test it on raspbian, I get errors Access denied for user 'testuser'@'localhost' (using password: YES).
It works after I follow these 2 suggestions
- https://stackoverflow.com/questions/6091427/mysql-into-outfile-access-denied-but-my-user-has-all-access-and-the-fold GRANT FILE ON *.* TO 'asdfsdf'@'localhost';
- https://www.raspberrypi.org/forums/viewtopic.php?p=1326803 Creating a new directory and change directory attributes mkdir /tmp/data; chmod 777 /tmp/data
Following the exercise here,
$ mysql -u testuser -ptestpass -e \ "Use testrdb; SELECT * FROM motortrend WHERE model = 'RX4' INTO OUTFILE '/tmp/data/rx4.csv'" $ cat /tmp/data/rx4.csv Mazda RX4 21 6 160 110 3.9 2.62 16.46 0 1 4 4 Mazda RX4 newrow 21 \N \N \N \N \N \N \N \N \N \N \N RX4
Create a table
Note that a database have multiple tables.
- How to Create a Table in SQL
- https://dev.mysql.com/doc/refman/5.7/en/creating-database.html
- https://dev.mysql.com/doc/refman/5.7/en/creating-tables.html
mysql> CREATE DATABASE testdb; mysql> USE testdb mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
OR put it in a text file
mysql> CREATE TABLE pet { name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE }
Show all tables.
mysql> SHOW TABLES;
Verify your table
mysql> DESCRIBE pet;
Loading data into a table
https://dev.mysql.com/doc/refman/5.7/en/loading-tables.html
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
OR using the mysqlimport utility from the shell
$ mysqlimport --local pet /path/pet.txt
You could add a new record using an INSERT statement like this:
mysql> INSERT INTO pet -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Retrieve data
https://dev.mysql.com/doc/refman/5.7/en/retrieving-data.html General form
SELECT what_to_select FROM which_table WHERE conditions_to_satisfy;
Some examples
mysql> SELECT * FROM pet; mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser'; mysql> SELECT * FROM pet WHERE name = 'Bowser'; mysql> SELECT * FROM pet WHERE birth >= '1998-1-1'; mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f'; mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') -> OR (species = 'dog' AND sex = 'f'); mysql> SELECT name, birth FROM pet; mysql> SELECT DISTINCT owner FROM pet; mysql> SELECT name, birth FROM pet ORDER BY birth; mysql> SELECT name, birth FROM pet ORDER BY birth DESC; # To sort in reverse (descending) order mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
mysql> SELECT * FROM pet WHERE name LIKE 'b%'; # find names beginning with b, case-insensitive mysql> SELECT * FROM pet WHERE name LIKE '%fy'; # find names ending with fy: mysql> SELECT * FROM pet WHERE name LIKE '%w%'; # find names containing a w:
Count rows
mysql> SELECT COUNT(*) FROM pet; mysql> SELECT COUNT(DISTINCT owner) FROM pet; mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
Retrieve information from multiple tables https://dev.mysql.com/doc/refman/5.7/en/multiple-tables.html
# suppose we have two tables event & score; each has a column called event_id # score table has columns: name, event_id, score # event table has columns: event_id, date, type mysql> SELECT student_id, date, score, type -> FROM event, score -> WHERE date = "1999-09-20" -> AND event.event_id = score.event_id;
Mysql list tables and sizes - order by size
https://stackoverflow.com/questions/14569940/mysql-list-tables-and-sizes-order-by-size
SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = "schema_name" ORDER BY (data_length + index_length) DESC;
Note: replace "schema_name" with your database name.
Find first and last record from mysql table
https://stackoverflow.com/questions/2735395/how-to-find-first-and-last-record-from-mysql-table
# get the fields name Show columns from tablename; # To get the first record: select col1 from tab1 order by col1 asc limit 1; # To get the last record: select col1 from tab1 order by col1 desc limit 1; # To get the last few records select * from tab1 order by col2 desc limit 10;
Write/save a table/query to a text file
- http://zetcode.com/databases/mysqltutorial/exportimport/
- https://stackoverflow.com/questions/21253704/how-to-save-mysql-query-output-to-excel-or-txt-file
- https://stackoverflow.com/questions/6091427/mysql-into-outfile-access-denied-but-my-user-has-all-access-and-the-fold
- Sample MySQL database file <mysqlsampledatabase.sql>
# Method 1. mysql. Get a permission denied error $ mysql -u root -p > SELECT * FROM tbl_name INTO OUTFILE '/tmp/orders.txt'; # Method 2. shell. Simple solution; does not need to change FILE privileges. # https://stackoverflow.com/questions/9462416/shell-one-line-query mysql -u root -pmy_password -h ip_add --port=13306 -D DATABASENAME -e "SELECT * FROM tbl_name;" > output.txt
Create a database 'demo' and a table 'employee'
http://www.thegeekstuff.com/2011/10/mysql-tutorial-basics/
mysql -u guest -p demo show databases; use demo; show tables; create table employee .............; desc employee; insert into employee .............; select * from employee;
Access mysql using perl
http://www.cyberciti.biz/faq/how-to-access-mysql-database-using-perl
How to See Which MySQL Tables are Taking the Most Space
https://www.howtogeekpro.com/166/how-to-see-which-mysql-tables-are-taking-the-most-space/
Check a Value in a MySQL Database from a Linux Bash Script
Check a Value in a MySQL Database from a Linux Bash Script
Dates and times
How to Work Effectively With Dates and Times in MySQL
Administration
Default port
3306
phpMyAdmin
http://localhost/phpmyadmin with default username: root and the admin password you have chosen during the installation of phpMyAdmin.
- Apache -> phpMyAdmin
- https://www.digitalocean.com/community/tutorials/how-to-install-and-secure-phpmyadmin-on-ubuntu-16-04
- http://tuxtweaks.com/2011/10/install-lamp-and-phpmyadmin-on-ubuntu-11-10/2/
phpMyAdmin is installed under /usr/share/phpmyadmin.
Note that index.php file does not appear in /var/www/html directory. Why? A standard Apache Alias pointing every request starting with /phpmyadmin to the phpMyAdmin installation directory. See Setting up and securing a phpMyAdmin install on Ubuntu 10.04
udooer@udoo:~$ ls -l /var/www/html total 12 -rw-r--r-- 1 root root 11510 Dec 24 13:44 index.html lrwxrwxrwx 1 root root 18 Dec 24 14:00 mediawiki -> /var/lib/mediawiki udooer@udoo:~$ ls -lah /etc/apache2/ total 88K drwxr-xr-x 8 root root 4.0K Dec 24 13:44 . drwxr-xr-x 121 root root 4.0K Dec 25 08:15 .. -rw-r--r-- 1 root root 7.0K Jan 7 2014 apache2.conf drwxr-xr-x 2 root root 4.0K Dec 25 08:15 conf-available drwxr-xr-x 2 root root 4.0K Dec 25 08:15 conf-enabled -rw-r--r-- 1 root root 1.8K Jan 3 2014 envvars -rw-r--r-- 1 root root 31K Jan 3 2014 magic drwxr-xr-x 2 root root 12K Dec 24 13:45 mods-available drwxr-xr-x 2 root root 4.0K Dec 24 13:45 mods-enabled -rw-r--r-- 1 root root 320 Jan 7 2014 ports.conf drwxr-xr-x 2 root root 4.0K Dec 24 13:44 sites-available drwxr-xr-x 2 root root 4.0K Dec 24 13:44 sites-enabled udooer@udoo:~$ ls -laH /etc/apache2/sites-available/ total 20 drwxr-xr-x 2 root root 4096 Dec 24 13:44 . drwxr-xr-x 8 root root 4096 Dec 24 13:44 .. -rw-r--r-- 1 root root 1332 Jan 7 2014 000-default.conf -rw-r--r-- 1 root root 6437 Jan 7 2014 default-ssl.conf udooer@udoo:~$ ls -lah /etc/apache2/conf-enabled/ total 8.0K drwxr-xr-x 2 root root 4.0K Dec 25 08:15 . drwxr-xr-x 8 root root 4.0K Dec 24 13:44 .. lrwxrwxrwx 1 root root 30 Dec 24 13:44 charset.conf -> ../conf-available/charset.conf lrwxrwxrwx 1 root root 40 Dec 25 08:13 javascript-common.conf -> ../conf-available/javascript-common.conf lrwxrwxrwx 1 root root 44 Dec 24 13:44 localized-error-pages.conf -> ../conf-available/localized-error-pages.conf lrwxrwxrwx 1 root root 46 Dec 24 13:44 other-vhosts-access-log.conf -> ../conf-available/other-vhosts-access-log.conf lrwxrwxrwx 1 root root 33 Dec 25 08:15 phpmyadmin.conf -> ../conf-available/phpmyadmin.conf lrwxrwxrwx 1 root root 31 Dec 24 13:44 security.conf -> ../conf-available/security.conf lrwxrwxrwx 1 root root 36 Dec 24 13:44 serve-cgi-bin.conf -> ../conf-available/serve-cgi-bin.conf udooer@udoo:~$ cat /etc/apache2/conf-available/phpmyadmin.conf # phpMyAdmin default Apache configuration Alias /phpmyadmin /usr/share/phpmyadmin <Directory /usr/share/phpmyadmin> Options FollowSymLinks DirectoryIndex index.php <IfModule mod_php5.c> AddType application/x-httpd-php .php php_flag magic_quotes_gpc Off php_flag track_vars On php_flag register_globals Off php_admin_flag allow_url_fopen Off php_value include_path . php_admin_value upload_tmp_dir /var/lib/phpmyadmin/tmp php_admin_value open_basedir /usr/share/phpmyadmin/:/etc/phpmyadmin/:/var/lib/phpmyadmin/:/usr/share/php/php-gettext/:/usr/share/javascript/ </IfModule> </Directory>
If we want to change the URL or the port number of phpMyAdmin, follow this.
Configuration files
MySQL Docker Containers: Understanding the basics
On Ubuntu 18.04
$ sudo apt update $ sudo apt install mariadb-server mariadb-client $ sudo systemctl status mariadb $ sudo mysql_secure_installation $ mysql –u root -p mysql Ver 15.1 Distrib 10.1.41-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Usage: mysql [OPTIONS] [database] Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf ...
Ubuntu 18.04 configuration file location /etc/mysql/mariadb.conf.d/50-server.cnf
This can be found by using mysql --help or mysqld --help --verbose. On Ubuntu 16.04, it shows
$ mysql --help ... Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf ... $ tree /etc/mysql/ /etc/mysql/ ├── conf.d │ ├── mysql.cnf │ └── mysqldump.cnf ├── debian.cnf ├── debian-start ├── my.cnf -> /etc/alternatives/my.cnf ├── my.cnf.fallback ├── mysql.cnf └── mysql.conf.d ├── mysqld.cnf └── mysqld_safe_syslog.cnf 2 directories, 9 files
Errors
- Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (38)
- Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (on Debian 10) when I run sudo mysql_secure_installation after I installed mariadb-server.
Solution: Make sure to run the following after installing mariadb-server
sudo systemctl start mariadb.service sudo systemctl enable mariadb.service
Default database location
On Debian/Ubuntu, it is /var/lib/mysql.
$ mysql -uroot -p -e 'SHOW VARIABLES WHERE Variable_Name = "datadir"'
OR
mysql> select @@datadir;
Change data location
https://stackoverflow.com/questions/1795176/how-to-change-mysql-data-directory
- sudo /etc/init.d/mysql stop
- sudo cp -R -p /var/lib/mysql /newpath
- sudo gedit /etc/mysql/my.cnf
- Look for the entry for datadir, and change the path (which should be /var/lib/mysql) to the new data directory.
- sudo gedit /etc/apparmor.d/usr.sbin.mysqld
- Look for lines beginning with /var/lib/mysql. Change /var/lib/mysql in the lines with the new path.
- sudo /etc/init.d/apparmor reload
- sudo /etc/init.d/mysql restart
Full Backup and Restore with Mariabackup
- The mariabackup command is a physical backup method (cf SQL command is a logical backup method)
- A physical backup involves copying the data files directly. It is faster and more efficient, but less portable. These data files are specific to the hardware, operating system, and version of MariaDB that you are using.
- A logical backup exports the data in a human-readable format such as SQL, while a physical backup copies the raw data files directly.
- MariaDB Backup or here
- You can restore the backup using the rsync command (as long as the MariaDB Server process is stopped on the target server, you can technically restore the backup using any file copying tool, such as cp or rysnc). sudo systemctl restart mysqld
$ mariabackup --backup \ --target-dir=/var/mariadb/backup/ \ --user=mariabackup --password=mypassword
$ mariabackup --prepare \ --target-dir=/var/mariadb/backup/ $ mariabackup --copy-back \ --target-dir=/var/mariadb/backup/
How to backup/export and load/import a single database and/or table from a MySQL database
- https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
- https://www.howtogeekpro.com/157/how-to-backup-export-a-single-table-from-a-mysql-database/
- https://stackoverflow.com/questions/4546778/how-can-i-import-a-database-with-mysql-from-terminal
- How to Exclude Tables from MySQL Exports Using mysqldump
# Export # Method 1: directly $ mysqldump -u USERNAME --password="PW" DB_NAME > backup.sql # Method 2: gzip $ mysqldump -u USERNAME --password="PW" DB_NAME | gzip --best --verbose > backup.sql.gz # Multiple database $ mysqldump -u USERNAME --password="PW" --all-databases > all_databases.sql # Import $ mysql -u UserName -p Password -e 'Create Database DB_NAME' # Method 1 (the database needs to be created first) $ mysql -u UserName -p Password -h Hostname DB_NAME < backup.sql # Method 2 (not working?) $ mysqlimport -u UserName -p Password backup.sql # Multiple database (no need to create an empty database) $ mysql -u UserName -p < backup.sql
Note that
- the back up file is a text file.
- it seems it is common to use sql as the extension name
- the user information will not be retained in the backup file.
- the original database name is saved in the backup file
- the database name DB_NAME in importing is required. It the database name does not to be the same as the original if only one database was dumped. However the database DB_NAME has to be created beforehand (mysql> CREATE DATABASE DB_NAME2). If the database has not existed or we omit the DB_NAME in importing, we will get an error
ERROR 1046 (3D000) at line 22: No database selected # or ERROR 1049 (42000): Unknown database 'testdb'
Export a CSV file
How to export a CSV file from MySQL command line
Move the MySQL data directory
- https://askubuntu.com/questions/137424/how-do-i-move-the-mysql-data-directory
- How To Move a MySQL Data Directory to a New Location on Ubuntu 16.04
SSL
How to set up MariaDB SSL and secure connections from clients
Secure Your Database
How Should You Secure Your Database?
Injection attack
How to Analyse MySQL Performance
How to Analyse MySQL Performance Problems
Load balance
How to Install a Load Balancing MySQL Server with ProxySQL on Debian 11
Office
LibreOffice
Introduction to Databases: LibreOffice Base Tutorial (youtube)
Use through R
Examples from r-bloggers
- http://www.r-bloggers.com/mysql-and-r/
- http://www.r-bloggers.com/accessing-mysql-through-r/
- http://gettinggeneticsdone.blogspot.com/2011/05/accessing-databases-from-r-rstats-sql.html
- useR Vignette from Jeffrey Breen
- Phil Spector
Installation
- http://cran.r-project.org/web/packages/RMySQL/
- https://db.rstudio.com/my-sql/ (more instructions)
Ubuntu/Debian
First, log in using root.
install.packages("DBI")
Go to shell and
# sudo apt-get install libdbd-mysql (this line seems not necessary) # sudo apt-get install libmysqlclient-dev (Ubuntu 14) sudo apt-get install -y libmariadb-dev (Debian Stretch) # R # install.packages("RMySQL")
Windows
Check my note with complete screenshots at File:Install MySQL on Windows.pdf.
Open and close connection: dbConnect/dbDisconnect
con <- dbConnect(MySQL(), user="me", password="nuts2u", dbname="my_db", host="localhost") on.exit(dbDisconnect(con)) dbListTables(con) head(dbReadTable(con, "recentchanges"))
List tables and fields: dbListTables and dbListFields
dbListTables(mydb) dbListFields(mydb, 'some_table')
Read and write entire tables: dbReadTable and dbWriteTable
We can create tables in the database using R dataframes.
df = dbReadTable(con, 'motortrend') dbWriteTable(mydb, name='table_name', value=data.frame.name)
Query: dbGetQuery and dbSendQuery
You can process query results row by row, in blocks or all at once. The highly useful function dbGetQuery(con, sql) returns all query results as a data frame. With dbSendQuery, you can get all or partial results with fetch.
con <- dbConnect(MySQL(), user="network_portal", password="monkey2us", dbname=db.name, host="localhost") rs <- dbSendQuery(con, "select name from genes limit 10;") # results in in mysql data <- fetch(rs, n=10) # return result to R as a data frame; use n=-1 to retrieve all pending records. huh <- dbHasCompleted(rs) dbClearResult(rs) dbDisconnect(con)
Aggregate and Sort
df = dbGetQuery(con, "SELECT mfg, avg(hp) AS meanHP FROM motortrend GROUP BY mfg ORDER BY meanHP DESC") df = dbGetQuery(con, "SELECT cyl as cylinders, avg(hp) as meanHP FROM motortrend GROUP by cyl ORDER BY cyl")
dbApply()
sql = "SELECT cyl, hp FROM motortrend ORDER BY cyl" rs = dbSendQuery(con, sql) dbApply(rs, INDEX='cyl', FUN=function(x, grp) quantile(x$hp))
Retrieving AUTO_INCREMENT IDs
create.network <- function(species.id, network.name, data.source, description) { con <- dbConnect(MySQL(), user="super_schmuck", password="nuts2u", dbname="my_db", host="localhost") on.exit(dbDisconnect(con)) sql <- sprintf("insert into networks (species_id, name, data_source, description, created_at) values (%d, '%s', '%s', '%s', NOW());", species.id, network.name, data.source, description) rs <- dbSendQuery(con, sql) dbClearResult(rs) id <- dbGetQuery(con, "select last_insert_id();")[1,1] return(id) }
Full example from Jeffrey Breen
http://www.r-bloggers.com/slides-%E2%80%9Caccessing-databases-from-r%E2%80%9D-rstats/
First, create new database & user in MySQL:
mysql> create database testrdb; mysql> grant all privileges on testrdb.* to 'testuser'@'localhost' identified by 'testpass'; mysql> flush privileges;
In R, load the “mtcars” data.frame, clean it up, and write it to a new “motortrend” table:
library(stringr) library(RMySQL) data(mtcars) # car name is data.frame's rownames. Let's split into manufacturer and model columns: mtcars$mfg = str_split_fixed(rownames(mtcars), ' ', 2)[,1] mtcars$mfg[mtcars$mfg=='Merc'] = 'Mercedes' mtcars$model = str_split_fixed(rownames(mtcars), ' ', 2)[,2] > dim(mtcars) [1] 32 13 > mtcars[1:3,] mpg cyl disp hp drat wt qsec vs am gear carb mfg model Mazda RX4 21.0 6 160 110 3.90 2.620 16.46 0 1 4 4 Mazda RX4 Mazda RX4 Wag 21.0 6 160 110 3.90 2.875 17.02 0 1 4 4 Mazda RX4 Wag Datsun 710 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1 Datsun 710 # connect to local MySQL database (host='localhost' by default) con = dbConnect(MySQL(), "testrdb", username="testuser", password="testpass") dbWriteTable(con, 'motortrend', mtcars) dbDisconnect(con)
And check the database from mysql
mli@PhenomIIx6:~$ mysql -u testuser -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 153 Server version: 5.1.63-0ubuntu0.11.10.1 (Ubuntu) Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | testrdb | +--------------------+ 2 rows in set (0.00 sec) mysql> use testrdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +-------------------+ | Tables_in_testrdb | +-------------------+ | motortrend | +-------------------+ 1 row in set (0.00 sec) mysql> describe motortrend; +-----------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------+------+-----+---------+-------+ | row_names | text | YES | | NULL | | | mpg | double | YES | | NULL | | | cyl | double | YES | | NULL | | | disp | double | YES | | NULL | | | hp | double | YES | | NULL | | | drat | double | YES | | NULL | | | wt | double | YES | | NULL | | | qsec | double | YES | | NULL | | | vs | double | YES | | NULL | | | am | double | YES | | NULL | | | gear | double | YES | | NULL | | | carb | double | YES | | NULL | | | mfg | text | YES | | NULL | | | model | text | YES | | NULL | | +-----------+--------+------+-----+---------+-------+ 14 rows in set (0.01 sec) mysql> SELECT * FROM motortrend WHERE row_names like "Mazda%" order by mpg; +---------------+------+------+------+------+------+-------+-------+------+------+------+------+-------+---------+ | row_names | mpg | cyl | disp | hp | drat | wt | qsec | vs | am | gear | carb | mfg | model | +---------------+------+------+------+------+------+-------+-------+------+------+------+------+-------+---------+ | Mazda RX4 | 21 | 6 | 160 | 110 | 3.9 | 2.62 | 16.46 | 0 | 1 | 4 | 4 | Mazda | RX4 | | Mazda RX4 Wag | 21 | 6 | 160 | 110 | 3.9 | 2.875 | 17.02 | 0 | 1 | 4 | 4 | Mazda | RX4 Wag | +---------------+------+------+------+------+------+-------+-------+------+------+------+------+-------+---------+ 2 rows in set (0.00 sec) mysql> SELECT row_names, wt, model FROM motortrend WHERE row_names like "Mazda%" order by wt; +---------------+-------+---------+ | row_names | wt | model | +---------------+-------+---------+ | Mazda RX4 | 2.62 | RX4 | | Mazda RX4 Wag | 2.875 | RX4 Wag | +---------------+-------+---------+ 2 rows in set (0.00 sec) mysql>
Setting Up Raspberry Pi Temperature/Humidity Sensors for Data Analysis in R
Setting Up Raspberry Pi Temperature/Humidity Sensors for Data Analysis in R
How does importing a database affect the current database?
Continue from the database (testrdb) from the previous section.
$ mysql -u testuser -ptestpass -e "use testrdb; select count(*) from motortrend" +----------+ | count(*) | +----------+ | 32 | +----------+ $ mysql -u testuser -ptestpass -e "use testrdb; select * FROM motortrend WHERE model = 'RX4'" +-----------+------+------+------+------+------+------+-------+------+------+------+------+-------+-------+ | row_names | mpg | cyl | disp | hp | drat | wt | qsec | vs | am | gear | carb | mfg | model | +-----------+------+------+------+------+------+------+-------+------+------+------+------+-------+-------+ | Mazda RX4 | 21 | 6 | 160 | 110 | 3.9 | 2.62 | 16.46 | 0 | 1 | 4 | 4 | Mazda | RX4 | +-----------+------+------+------+------+------+------+-------+------+------+------+------+-------+-------+ # Modify a record $ mysql -u testuser -ptestpass -e "use testrdb; update motortrend SET mpg=22 WHERE model = 'RX4'" $ mysql -u testuser -ptestpass -e "use testrdb; update motortrend SET mfg='toyota' WHERE model = 'RX4'" # Remove a record. Use 'Limit' if necessary # https://stackoverflow.com/questions/18378190/how-to-delete-a-certain-row-from-mysql-table-with-same-column-values $ mysql -u testuser -ptestpass -e "use testrdb; DELETE FROM motortrend WHERE model = 'Corona'"; # Insert a record $ mysql -u testuser -ptestpass -e \ "use testrdb; INSERT INTO motortrend VALUES ('newrow',21, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'RX4')"; $ mysql -u testuser -ptestpass -e "use testrdb; select * FROM motortrend WHERE model = 'RX4'" +-----------+------+------+------+------+------+------+-------+------+------+------+------+--------+-------+ | row_names | mpg | cyl | disp | hp | drat | wt | qsec | vs | am | gear | carb | mfg | model | +-----------+------+------+------+------+------+------+-------+------+------+------+------+--------+-------+ | Mazda RX4 | 22 | 6 | 160 | 110 | 3.9 | 2.62 | 16.46 | 0 | 1 | 4 | 4 | toyota | RX4 | | newrow | 21 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | RX4 | +-----------+------+------+------+------+------+------+-------+------+------+------+------+--------+-------+ # Import. $ mysql -u testuser -ptestpass testrdb < testrdb.sql $ mysql -u testuser -ptestpass -e "use testrdb; select * FROM motortrend WHERE model = 'RX4'" +-----------+------+------+------+------+------+------+-------+------+------+------+------+-------+-------+ | row_names | mpg | cyl | disp | hp | drat | wt | qsec | vs | am | gear | carb | mfg | model | +-----------+------+------+------+------+------+------+-------+------+------+------+------+-------+-------+ | Mazda RX4 | 21 | 6 | 160 | 110 | 3.9 | 2.62 | 16.46 | 0 | 1 | 4 | 4 | Mazda | RX4 | +-----------+------+------+------+------+------+------+-------+------+------+------+------+-------+-------+ $ mysql -u testuser -ptestpass -e "use testrdb; select count(*) from motortrend" +----------+ | count(*) | +----------+ | 32 | +----------+
dbplyr
Generating SQL with {dbplyr} and sqlfluff
RODBC
SQLite
- How and When to Use Sqlite
- RSQLite package
- readwritesqlite: Enhanced Reading and Writing for 'SQLite' Databases
- pi-hole is using SQLite3
- How to Install SQLite and SQLite Browser on Ubuntu 20.04
- What Is SQLite and Why Is It So Popular?
How to Use DB Browser for SQLite
How to Use DB Browser for SQLite on Linux
PostgreSQL
- 8 PostgreSQL Examples to Install, Create DB & Table, Insert & Select Records
- How to set up a Postgres database on a Raspberry Pi
- Setting up RStudio Server, Shiny Server and PostgreSQL
pgcli
Pgcli: a command line interface for Postgres with auto-completion and syntax highlighting.
Docker server + R client
clientsdb - A docker image with clients comments, github, docker
This requires
- Installation of some library: "sudo apt install libpq-dev" (PostgreSQL library in Ubuntu)
- R's packages "DBI" and "RPostgres"
CouchDB
How to Install Apache CouchDB on Ubuntu 18.04 LTS
InFluxDB
- Used in Monitoring a Greenhouse with Ubuntu
- Grafana
- InfluxDB To Grafana: Visualizing Time Series Data in Real Time
- Bird Feeder Monitor V2.0
- Gravit designer snap app
MongoDB
- SQL vs. NoSQL: What's the Best Database for Your Next Project?. NoSQL's schemaless structure allows you to insert additional arbitrary data into documents.
- How to Install MongoDB on Ubuntu
- {hexmake} is one of the 5 Grand Prizes of the 2020 Shiny Contest
- How To Backup and Migrate a MongoDB Database
- Learning MongoDB from Lynda.com
- mongolite: Fast and Simple 'MongoDB' Client for R
- mongo Docker Official Images
DuckDB
Joining the flock from R: working with data on MotherDuck
Redis
Find database online
- http://www.inside-r.org/howto/finding-data-internet
- http://www.r-bloggers.com/list-of-public-databases-from-the-washington-post/
Install Apache HBase
Follow the Quick Start to downloaded hbase tar ball. Suppose we save the tar ball under ~/Downloads folder and extract it in the same directory. We shall edit conf/hbase-site.xml file according to their instruction. The following is my case.
$ tar xzvf hbase-0.98.3-hadoop2-bin.tar.gz $ cd hbase-0.98.3-hadoop2/ $ cat conf/hbase-site.xml <?xml version="1.0"?> <?xml-stylesheet type="text/xsl" href="configuration.xsl"?> <configuration> <property> <name>hbase.rootdir</name> <value>file:///home/brb/Downloads/hbase-0.98.3-hadoop2/hbase</value> </property> <property> <name>hbase.zookeeper.property.dataDir</name> <value>/home/brb/Downloads/hbase-0.98.3-hadoop2/zookeeper</value> </property> </configuration>
Before we follow the getting started guide to launch HBase, we shall make sure JAVA_HOME environment variable is created.
$ ls /usr/lib/java $ export JAVA_HOME=/usr/lib/jvm/java-6-openjdk-amd64
Note that the last line may be replaced by
export JAVA_HOME=$(readlink -f /usr/bin/javac | sed "s:bin/javac::")
Then we can launch HBase,
$ ./bin/start-hbase.sh starting master, logging to /home/brb/Downloads/hbase-0.98.3-hadoop2/bin/../logs/hbase-brb-master-brb-P45T-A.out brb@brb-P45T-A:~/Downloads/hbase-0.98.3-hadoop2$ ./bin/hbase shell 2014-07-06 09:51:34,621 INFO [main] Configuration.deprecation: hadoop.native.lib is deprecated. Instead, use io.native.lib.available HBase Shell; enter 'help<RETURN>' for list of supported commands. Type "exit<RETURN>" to leave the HBase Shell Version 0.98.3-hadoop2, rd5e65a9144e315bb0a964e7730871af32f5018d5, Sat May 31 19:56:09 PDT 2014 hbase(main):001:0> create 'test', 'cf' 2014-07-06 09:51:49,510 WARN [main] util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable 0 row(s) in 2.0770 seconds => Hbase::Table - test hbase(main):002:0> list 'test' TABLE test 1 row(s) in 0.0530 seconds => ["test"] hbase(main):003:0> exit brb@brb-P45T-A:~/Downloads/hbase-0.98.3-hadoop2$ ./bin/hbase shell2014-07-06 09:53:37,480 INFO [main] Configuration.deprecation: hadoop.native.lib is deprecated. Instead, use io.native.lib.available HBase Shell; enter 'help<RETURN>' for list of supported commands. Type "exit<RETURN>" to leave the HBase Shell Version 0.98.3-hadoop2, rd5e65a9144e315bb0a964e7730871af32f5018d5, Sat May 31 19:56:09 PDT 2014 hbase(main):001:0> list 'test' TABLE 2014-07-06 09:53:44,373 WARN [main] util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable test 1 row(s) in 1.4800 seconds => ["test"] hbase(main):002:0> put 'test', 'row1', 'cf:a', 'value1' 0 row(s) in 0.4460 seconds hbase(main):003:0> put 'test', 'row2', 'cf:b', 'value2' 0 row(s) in 0.0140 seconds hbase(main):004:0> put 'test', 'row3', 'cf:c', 'value3' 0 row(s) in 0.0050 seconds hbase(main):005:0> scan 'test' ROW COLUMN+CELL row1 column=cf:a, timestamp=1404654837532, value=value1 row2 column=cf:b, timestamp=1404654856976, value=value2 row3 column=cf:c, timestamp=1404654866298, value=value3 3 row(s) in 0.0560 seconds hbase(main):006:0> get 'test', 'row1' COLUMN CELL cf:a timestamp=1404654837532, value=value1 1 row(s) in 0.0280 seconds hbase(main):007:0> disable 'test' 0 row(s) in 1.6050 seconds hbase(main):008:0> drop 'test' 0 row(s) in 0.2290 seconds hbase(main):009:0> exit brb@brb-P45T-A:~/Downloads/hbase-0.98.3-hadoop2$