80
edits
Changes
Jump to navigation
Jump to search
Line 48:
Line 48: − + Line 55:
Line 55: − + + Line 62:
Line 63: − + Line 71:
Line 72: + + + + + + + + + + + + + + + + + + + + + + + + + + Line 84:
Line 111: + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + Line 92:
Line 167: − + − sv t /service/mysqld + − + + + − + − + − + + +
Wrong path for expand-template (
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
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 .
/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):
/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
/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==
you need to investigate the cause by inspecting two logs :
* service log
journalctl -u mariadb
* mariadb log
tail -f /var/log/mariadb/mariadb.log
===Corrupted user table===
Your error in mariadb log will include
ERROR: 130 Incorrect file format 'user'
This could mostly occurs after a power outage. mysql.user table is a MYSIAM type
# ll /var/lib/mysql/mysql/user.*
-rw-rw---- 1 mysql mysql 10630 3 jui 21:08 /var/lib/mysql/mysql/user.frm
-rw-rw---- 1 mysql mysql 488 3 jui 21:08 /var/lib/mysql/mysql/user.MYD
-rw-rw---- 1 mysql mysql 2048 3 jui 21:08 /var/lib/mysql/mysql/user.MYI
In this case you might see user.MYD or user.MYI with 0 byte size. If the issue is on MYI this is the index you should be able to rebuild, if it is on the MYD, this is the data, you will need a backup to restore from.
as root, first start mariadb without grant table
systemctl stop mariadb
/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 }}
then use mysql command line
mysqlcheck mysql
if wound any error try
mysqlcheck mysql --repair
if it fails then you needs to do a restore. You might have a dump in /home/e-smith/db/mysql/mysql.dump. Wishing it is up to date. I suggest you to copy it and just extract the part for the table you are missing.
You need what is under
--
-- Table structure for table `user`
--
and
--
-- Dumping data for table `user`
--
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
expand-template /var/lib/mysql/set.password
mysql mysql < /var/lib/mysql/set.password
mysqladmin shutdown
systemctl start mariadb
{{warning box| the line dumping the table mysql.user to the mariadb server will delete any existing entries in the table if you are using the default SME dump as it has a DROP TABLE IF EXISTS line. So do this only if you know what you are doing.}}
==Access MariaDB/MySQL using port from the localhost and local network==
==Access MariaDB/MySQL using port from the localhost and local network==
{{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
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.