MySQL: Difference between revisions

From Chorke Wiki
Jump to navigation Jump to search
← Older edit
VisualWikitext
No edit summary
 
(29 intermediate revisions by the same user not shown)
Line 1: Line 1:
{|
{|class='wikitable' style='width:100%;margin:-11px 0 6px 0'
|valign="top"|
|valign='top' style='width:50%'|
<syntaxhighlight lang="bash">
<syntaxhighlight style='margin:3px 0' lang='bash'>
sudo apt-get install mariadb-server mariadb-client
sudo apt-get install mariadb-server mariadb-client
sudo systemctl enable --now mariadb
sudo systemctl enable --now mariadb
Line 8: Line 8:
</syntaxhighlight>
</syntaxhighlight>


|valign="top"|
|valign='top' style='width:50%'|
<syntaxhighlight lang="bash">
<syntaxhighlight style='margin:3px 0' lang='bash'>
sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf
sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf
sudo systemctl restart mariadb.service
sudo systemctl restart mariadb.service
Line 19: Line 19:


==Authorization==
==Authorization==
{|
{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
|valign="top"|
!scope='col' style='text-align:left' colspan='2'|
'''MySQL » Auth » Unix'''
PhPMyAdmin
<syntaxhighlight lang="sql">
|-
!scope='col' style='width:50%'|MySQL » Auth » Unix
!scope='col' style='width:50%'|MySQL » Auth » Local
|-
|valign='top'|
<syntaxhighlight style='margin:3px 0' lang='sql'>
sudo su
sudo su
cat << DDL | mysql
cat << DDL | mysql
Line 31: Line 36:
</syntaxhighlight>
</syntaxhighlight>


|valign="top"|
|valign='top'|
'''MySQL » Auth » Local'''
<syntaxhighlight style='margin:3px 0' lang='sql'>
<syntaxhighlight lang="sql">
cat << DDL | mysql
cat << DDL | mysql
CREATE DATABASE IF NOT EXISTS chorke_orgwiki;
CREATE DATABASE IF NOT EXISTS chorke_orgwiki;
Line 41: Line 45:
DDL
DDL
</syntaxhighlight>
</syntaxhighlight>
|-
|-
|valign="top"|
!scope='col'|MySQL » Auth » Remote
'''MySQL » Auth » Remote'''
!scope='col'|
<syntaxhighlight lang="sql">
|-
|valign='top'|
<syntaxhighlight style='margin:3px 0' lang='sql'>
cat << DDL | mysql
cat << DDL | mysql
CREATE DATABASE IF NOT EXISTS chorke_orgwiki;
CREATE DATABASE IF NOT EXISTS chorke_orgwiki;
Line 54: Line 59:
</syntaxhighlight>
</syntaxhighlight>


|valign="top"|
|valign='top'|
|}
 
==Backup Restore==
{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
!scope='col' style='text-align:left'|
Backup Restore
|-
|valign='top'|
<syntaxhighlight style='margin:3px 0' lang='bash'>
BACKUP_DATE_TIME="$(date +'%Y%m%d-T%H%M')-Z$(date +'%z'|tr '+-' 'PM')"
echo -n password: ;read -s MYSQL_PWD;export MYSQL_PWD; echo
# password: sadaqah!
 
mysql    -h127.0.0.1 -P3306 -uchorke -Dchorke_orgwiki
mysqldump -h127.0.0.1 -P3306 -uchorke  chorke_orgwiki > ./chorke_orgwiki-${BACKUP_DATE_TIME}.dump
mysql    -h127.0.0.1 -P3306 -uchorke -Dchorke_orgwiki <  chorke_orgwiki-20241010-T1010-ZP0600.dump
</syntaxhighlight>
|}
 
==Community==
{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
!scope='col' style='text-align:left'|
Community
|-
|valign='top'|
<syntaxhighlight style='margin:3px 0' lang='bash'>
curl -fsSL https://repo.mysql.com/RPM-GPG-KEY-mysql-2023\
| sudo tee /etc/apt/keyrings/mysql.asc >/dev/null
 
DISTRIBUTION=$(. /etc/os-release && echo "${VERSION_CODENAME}");\
cat << SRC | sudo tee /etc/apt/sources.list.d/mysql.list >/dev/null
deb [arch=$(dpkg --print-architecture) signed-by=/etc/apt/keyrings/mysql.asc] http://repo.mysql.com/apt/ubuntu/ ${DISTRIBUTION} mysql-tools
deb [arch=$(dpkg --print-architecture) signed-by=/etc/apt/keyrings/mysql.asc] http://repo.mysql.com/apt/ubuntu/ ${DISTRIBUTION} mysql-8.4-lts
deb [arch=$(dpkg --print-architecture) signed-by=/etc/apt/keyrings/mysql.asc] http://repo.mysql.com/apt/ubuntu/ ${DISTRIBUTION} mysql-apt-config
SRC
 
echo 'apt-get update;echo;apt-get install -y mysql-server'|sudo bash
</syntaxhighlight>
|}
 
==Workbench==
{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
!scope='col' style='text-align:left'|
Workbench
|-
|valign='top'|
<syntaxhighlight style='margin:3px 0' lang='bash'>
wget -cq https://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-workbench-community_8.0.40-1ubuntu24.04_amd64.deb -P ${HOME}/Downloads
sudo dpkg -i ${HOME}/Downloads/mysql-workbench-community_8.0.40-1ubuntu24.04_amd64.deb
      rm -rf ${HOME}/Downloads/mysql-workbench-community_8.0.40-1ubuntu24.04_amd64.deb


sudo apt-get --fix-broken install
sudo apt-get -f          install
sudo apt purge  mysql-workbench-community
sudo apt remove mysql-workbench-community
sudo apt autoremove
</syntaxhighlight>
|}
|}


== References==
==PhPMyAdmin==
{|
{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
|valign="top"|
!scope='col' style='text-align:left' colspan='2'|
PhPMyAdmin
|-
|valign='top' style='width:50%'|
<syntaxhighlight style='margin:3px 0' lang='bash'>
cat << DDL | mysql
CREATE DATABASE IF NOT EXISTS phpmyadmin;
CREATE USER 'phpmyadmin'@'%' IDENTIFIED BY 'sadaqah!';
GRANT ALL PRIVILEGES ON phpmyadmin.* TO 'phpmyadmin'@'%';
FLUSH PRIVILEGES;
DDL
 
sudo apt install phpmyadmin
 
sudo apt purge  phpmyadmin
sudo apt remove  phpmyadmin
sudo apt autoremove
</syntaxhighlight>
 
|valign='top' style='width:50%'|
|}
 
==Playground==
{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
!scope='col' style='text-align:left' colspan='2'|
Playground
|-
|valign='top' style='width:50%'|
<syntaxhighlight style='margin:3px 0' lang='sql'>
SELECT @@character_set_database, @@collation_database;
SHOW CHARACTER SET LIKE 'utf%';
SHOW CHARACTER SET;
</syntaxhighlight>
 
|valign='top' style='width:50%'|
<syntaxhighlight style='margin:3px 0' lang='sql'>
show databases;
use academia;
show tables;
</syntaxhighlight>
|-
|valign='top'|
<syntaxhighlight style='margin:3px 0' lang='sql'>
SELECT sysdate();
SELECT curdate();
SELECT now();
</syntaxhighlight>
|valign='top'|
|-
|valign='top' colspan='2'|
<syntaxhighlight style='margin:3px 0' lang='bash'>
gpg --keyring /usr/share/keyrings/mysql-apt-config.gpg --no-default-keyring --export -a > mysql.asc
gpg --enarmor /usr/share/keyrings/mysql-apt-config.gpg
          cat /usr/share/keyrings/mysql-apt-config.gpg.asc
</syntaxhighlight>
|}
 
==References==
{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
!scope='col' style='text-align:left' colspan='3'|
References
|-
|valign='top' style='width:33%'|
* [https://dev.mysql.com/downloads/workbench/ MySQL » Workbench » Community Download]
* [https://dev.mysql.com/downloads/repo/apt/ MySQL » Ubuntu » Community Download]
* [https://www.hostinger.my/tutorials/how-to-install-and-setup-phpmyadmin-on-ubuntu MySQL » Ubuntu » phpMyAdmin » Install]
* [https://www.hostinger.my/tutorials/how-to-install-and-setup-phpmyadmin-on-ubuntu MySQL » Ubuntu » phpMyAdmin » Install]
* [https://dev.mysql.com/blog-archive/accessing-the-same-data-through-ldap-and-sql/ MySQL » LDAP Store & Fetch Data]
* [https://dev.mysql.com/blog-archive/accessing-the-same-data-through-ldap-and-sql/ MySQL » LDAP Store & Fetch Data]
* [https://docs.vultr.com/how-to-install-mariadb-on-ubuntu-24-04 MySQL » Ubuntu » 24.04 » Install]  
* [https://docs.vultr.com/how-to-install-mariadb-on-ubuntu-24-04 MySQL » Ubuntu » 24.04 » Install]
* [http://repo.mysql.com/apt/ubuntu/ MySQL » Repo » Ubuntu]
* [[MySQL/Dump|MySQL » Dump]]
* [[MySQL/Dump|MySQL » Dump]]
* [[Helm/MariaDB|MySQL » Helm]]
* [https://repo.mysql.com/ MySQL » Repo]


|valign="top"|
|valign='top' style='width:34%'|


|valign="top"|
|valign='top' style='width:33%'|
 
|-
|colspan="3"|
----
|-
|-
|valign="top"|
|valign='top'|
* [[K8s/Ingress]]
* [[PostgreSQL]]
* [[PostgreSQL]]
* [[OpenLDAP]]
* [[OpenLDAP]]
* [[HAProxy]]
* [[Helm]]
* [[Helm]]
* [[CIDR]]
* [[UFW]]


|valign="top"|
|valign='top'|


|valign="top"|
|valign='top'|


|}
|}

Latest revision as of 07:34, 16 January 2026

sudo apt-get install mariadb-server mariadb-client
sudo systemctl enable --now mariadb
systemctl status mariadb
mariadb --version

sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf
sudo systemctl restart mariadb.service
systemctl status mariadb.service
sudo mysql_secure_installation

Authorization

PhPMyAdmin

MySQL » Auth » Unix MySQL » Auth » Local 
sudo su
cat << DDL | mysql
CREATE USER 'chorke'@'localhost' IDENTIFIED VIA unix_socket;
GRANT ALL PRIVILEGES ON *.* TO 'chorke'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
DDL

cat << DDL | mysql
CREATE DATABASE IF NOT EXISTS chorke_orgwiki;
CREATE USER 'chorke_orgwiki'@'localhost' IDENTIFIED BY 'sadaqah!';
GRANT ALL PRIVILEGES ON chorke_orgwiki.* TO 'chorke_orgwiki'@'localhost';
FLUSH PRIVILEGES;
DDL
MySQL » Auth » Remote 
cat << DDL | mysql
CREATE DATABASE IF NOT EXISTS chorke_orgwiki;
CREATE USER 'chorke_orgwiki'@'%' IDENTIFIED BY 'sadaqah!';
GRANT ALL PRIVILEGES ON chorke_orgwiki.* TO 'chorke_orgwiki'@'%';
FLUSH PRIVILEGES;
DDL

Backup Restore

Backup Restore 

BACKUP_DATE_TIME="$(date +'%Y%m%d-T%H%M')-Z$(date +'%z'|tr '+-' 'PM')"
echo -n password: ;read -s MYSQL_PWD;export MYSQL_PWD; echo
# password: sadaqah!

mysql     -h127.0.0.1 -P3306 -uchorke -Dchorke_orgwiki
mysqldump -h127.0.0.1 -P3306 -uchorke   chorke_orgwiki > ./chorke_orgwiki-${BACKUP_DATE_TIME}.dump
mysql     -h127.0.0.1 -P3306 -uchorke -Dchorke_orgwiki <   chorke_orgwiki-20241010-T1010-ZP0600.dump

Community

Community 

curl -fsSL https://repo.mysql.com/RPM-GPG-KEY-mysql-2023\
 | sudo tee /etc/apt/keyrings/mysql.asc >/dev/null

DISTRIBUTION=$(. /etc/os-release && echo "${VERSION_CODENAME}");\
cat << SRC | sudo tee /etc/apt/sources.list.d/mysql.list >/dev/null
deb [arch=$(dpkg --print-architecture) signed-by=/etc/apt/keyrings/mysql.asc] http://repo.mysql.com/apt/ubuntu/ ${DISTRIBUTION} mysql-tools
deb [arch=$(dpkg --print-architecture) signed-by=/etc/apt/keyrings/mysql.asc] http://repo.mysql.com/apt/ubuntu/ ${DISTRIBUTION} mysql-8.4-lts
deb [arch=$(dpkg --print-architecture) signed-by=/etc/apt/keyrings/mysql.asc] http://repo.mysql.com/apt/ubuntu/ ${DISTRIBUTION} mysql-apt-config
SRC

echo 'apt-get update;echo;apt-get install -y mysql-server'|sudo bash

Workbench

Workbench 

wget -cq https://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-workbench-community_8.0.40-1ubuntu24.04_amd64.deb -P ${HOME}/Downloads
sudo dpkg -i ${HOME}/Downloads/mysql-workbench-community_8.0.40-1ubuntu24.04_amd64.deb
      rm -rf ${HOME}/Downloads/mysql-workbench-community_8.0.40-1ubuntu24.04_amd64.deb

sudo apt-get --fix-broken install
sudo apt-get -f           install

sudo apt purge  mysql-workbench-community
sudo apt remove mysql-workbench-community
sudo apt autoremove

PhPMyAdmin

PhPMyAdmin 

cat << DDL | mysql
CREATE DATABASE IF NOT EXISTS phpmyadmin;
CREATE USER 'phpmyadmin'@'%' IDENTIFIED BY 'sadaqah!';
GRANT ALL PRIVILEGES ON phpmyadmin.* TO 'phpmyadmin'@'%';
FLUSH PRIVILEGES;
DDL

sudo apt install phpmyadmin

sudo apt purge   phpmyadmin
sudo apt remove  phpmyadmin
sudo apt autoremove

Playground

Playground 

SELECT @@character_set_database, @@collation_database;
SHOW CHARACTER SET LIKE 'utf%';
SHOW CHARACTER SET;

show databases;
use academia;
show tables;

SELECT sysdate();
SELECT curdate();
SELECT now();

gpg --keyring /usr/share/keyrings/mysql-apt-config.gpg --no-default-keyring --export -a > mysql.asc
gpg --enarmor /usr/share/keyrings/mysql-apt-config.gpg
          cat /usr/share/keyrings/mysql-apt-config.gpg.asc

References

References

Retrieved from "https://wiki.chorke.org/index.php?title=MySQL&oldid=16149"