Line 48: |
Line 48: |
| To reset the password for the MySQL root account. The MySQL root user on SME Server has a random generated password which is generated during installation. You do not need to know this password to login to MySQL with root privileges on SME Server. If you might have changed the MySQL root password you can reset it like this after getting command line access as root user. | | To reset the password for the MySQL root account. The MySQL root user on SME Server has a random generated password which is generated during installation. You do not need to know this password to login to MySQL with root privileges on SME Server. If you might have changed the MySQL root password you can reset it like this after getting command line access as root user. |
| | | |
− | For SME 10 (note for SCLO version see respective contrib page): | + | <tabs container style="display: inline-block;"><tab name="For SME 10"> |
| systemctl stop mariadb | | systemctl stop mariadb |
| expand-template /root/.my.cnf | | expand-template /root/.my.cnf |
Line 55: |
Line 55: |
| exit | | exit |
| systemctl start mariadb | | systemctl start mariadb |
− | For SME Server 7.3 and up to SME 9.2: | + | </tab><tab name="For SME 7.3 and up to SME 9.2"> |
| + | |
| cd /var/service/mysqld | | cd /var/service/mysqld |
| sv d . | | sv d . |
Line 62: |
Line 63: |
| /usr/libexec/mysqld --bootstrap --user=mysql --skip-grant-tables < ./set.password | | /usr/libexec/mysqld --bootstrap --user=mysql --skip-grant-tables < ./set.password |
| sv u . | | sv u . |
− | | + | </tab><tab name="For SME 7.2 and earlier"> |
| For SME Server 7.2 and earlier releases do the following (they use the runsvctrl command instead of the sv command): | | For SME Server 7.2 and earlier releases do the following (they use the runsvctrl command instead of the sv command): |
| | | |
Line 71: |
Line 72: |
| /usr/libexec/mysqld --bootstrap --user=mysql --skip-grant-tables < ./set.password | | /usr/libexec/mysqld --bootstrap --user=mysql --skip-grant-tables < ./set.password |
| runsvctrl u . | | runsvctrl u . |
| + | </tab> |
| + | </tabs> |
| | | |
| ===Restoring accidentally deleted MySQL root user=== | | ===Restoring accidentally deleted MySQL root user=== |
| + | <tabs container style="display: inline-block;"><tab name="For SME 10"> |
| + | mariadb 5.5 and up to 10.5 |
| + | systemctl stop mariadb |
| + | echo "GRANT ALL PRIVILEGES ON *.* TO 'root'@'`config get DomainName`' WITH GRANT OPTION;">/var/lib/mysql/set.rootuser |
| + | echo "GRANT PROXY ON ''@'' TO 'root'@'`config get DomainName`' WITH GRANT OPTION;">>/var/lib/mysql/set.rootuser |
| + | echo "GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;">>/var/lib/mysql/set.rootuser |
| + | echo "GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;">>/var/lib/mysql/set.rootuser |
| + | expand-template /root/.my.cnf |
| + | expand-template /var/lib/mysql/set.password |
| + | /usr/libexec/mysqld --socket=/var/lib/mysql/mysql.sock --bootstrap --user=mysql --skip-grant-tables <( cat /var/lib/mysql/set.rootuser /var/lib/mysql/set.password) |
| + | exit |
| + | systemctl start mariadb |
| + | </tab><tab name="For SME 8 and up to 9"> |
| + | for MySQL 5.1.73 |
| + | cd /var/service/mysqld |
| + | sv d . |
| + | echo 'use mysql;'>set.rootuser |
| + | echo "INSERT INTO `user` VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0);">>set.rootuser |
| + | expand-template /root/.my.cnf |
| + | expand-template /var/service/mysqld/set.password |
| + | /usr/libexec/mysqld --bootstrap --user=mysql --skip-grant-tables < set.rootuser |
| + | /usr/libexec/mysqld --bootstrap --user=mysql --skip-grant-tables < set.password |
| + | sv u . |
| + | </tab><tab name="For SME 7.3"> |
| Note: The following is only applicable on SME 7.3 and MySQL 4.1 | | Note: The following is only applicable on SME 7.3 and MySQL 4.1 |
| cd /var/service/mysqld | | cd /var/service/mysqld |
Line 84: |
Line 111: |
| /usr/libexec/mysqld --bootstrap --user=mysql --skip-grant-tables < set.password | | /usr/libexec/mysqld --bootstrap --user=mysql --skip-grant-tables < set.password |
| sv u . | | sv u . |
| + | </tab> |
| + | </tabs> |
| | | |
| ==MariaDB/MySQL fails to start== | | ==MariaDB/MySQL fails to start== |
Line 106: |
Line 135: |
| as root, first start mariadb without grant table | | as root, first start mariadb without grant table |
| systemctl stop mariadb | | systemctl stop mariadb |
− | /usr/libexec/mysqld --bootstrap --user=mysql --skip-grant-tables | + | /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/mysql --user=mysql --skip-grant-tables |
| {{warning box| be careful that mariadb will be running without any user auth, if it is open to outside of your server, then you might want to close this access first, and keep this session as short as possible }} | | {{warning box| be careful that mariadb will be running without any user auth, if it is open to outside of your server, then you might want to close this access first, and keep this session as short as possible }} |
| then use mysql command line | | then use mysql command line |
Line 124: |
Line 153: |
| Considering your table dump is now in a file called /home/e-smith/db/mysql/mysql.user.dump, do | | Considering your table dump is now in a file called /home/e-smith/db/mysql/mysql.user.dump, do |
| mysql mysql < /home/e-smith/db/mysql/mysql.user.dump | | mysql mysql < /home/e-smith/db/mysql/mysql.user.dump |
− | expand-template /var/service/mysqld/set.password | + | expand-template /var/lib/mysql/set.password |
− | mysql mysql < /var/service/mysqld/set.password | + | mysql mysql < /var/lib/mysql/set.password |
| mysqladmin shutdown | | mysqladmin shutdown |
| systemctl start mariadb | | systemctl start mariadb |
Line 138: |
Line 167: |
| {{Warning box|Keep in mind that by default MariaDB/MySQL is not using any kind of encryption unless you did work on that yourself, so any access to the port from the LAN will be as clear text and anyone on the LAN will be able to access to the password and all the data transferred between your server and the client. Refers to the manual of your database version.}} | | {{Warning box|Keep in mind that by default MariaDB/MySQL is not using any kind of encryption unless you did work on that yourself, so any access to the port from the LAN will be as clear text and anyone on the LAN will be able to access to the password and all the data transferred between your server and the client. Refers to the manual of your database version.}} |
| | | |
− | For SME 10: | + | <tabs container style="display: inline-block;"><tab name="For SME 10"> |
| config setprop mariadb LocalNetworkingOnly no | | config setprop mariadb LocalNetworkingOnly no |
| expand-template /etc/my.cnf | | expand-template /etc/my.cnf |
− | sv t /service/mysqld | + | systemctl restart /service/mysqld |
− | For SME 9 and below: | + | </tab><tab name="For SME 9.2 and before"> |
| config setprop mysqld LocalNetworkingOnly no | | config setprop mysqld LocalNetworkingOnly no |
| expand-template /etc/my.cnf | | expand-template /etc/my.cnf |
| sv t /service/mysqld | | sv t /service/mysqld |
| + | </tab> |
| + | </tabs> |
| | | |
| ==Access MySQL from a remote network== | | ==Access MySQL from a remote network== |
| If you wish to enable access to MariaDB/MySQL databases from remote networks, then in addition to the LocalNetworkingOnly db setting mentioned above, you will need to execute the following commands: | | If you wish to enable access to MariaDB/MySQL databases from remote networks, then in addition to the LocalNetworkingOnly db setting mentioned above, you will need to execute the following commands: |
| | | |
− | For SME 10 | + | <tabs container style="display: inline-block;"><tab name="For SME 10"> |
| config set mariadb service access public status enabled TCPPort 3306 | | config set mariadb service access public status enabled TCPPort 3306 |
| signal-event remoteaccess-update | | signal-event remoteaccess-update |
− | signal-event reboot | + | signal-event smeserver-mysql-update |
− | For SME9 and below | + | </tab><tab name="For SME 9 and below"> |
| config set mysqld service access public status enabled TCPPort 3306 | | config set mysqld service access public status enabled TCPPort 3306 |
| signal-event remoteaccess-update | | signal-event remoteaccess-update |
| signal-event reboot | | signal-event reboot |
| + | </tab> |
| + | </tabs> |
| | | |
| Keep in mind this enables access to your MariaDB/MySQL database for ANYONE, so make sure you have strong passwords on ALL your MariaDB/MySQL databases. | | Keep in mind this enables access to your MariaDB/MySQL database for ANYONE, so make sure you have strong passwords on ALL your MariaDB/MySQL databases. |