Installing a new database server: Difference between revisions
(5 intermediate revisions by the same user not shown) | |||
Line 12: | Line 12: | ||
</pre> | </pre> | ||
to set up a root password and delete the test database | to set up a root password and delete the test database | ||
If you want the databases to run from the /home/ directory (or a subdir) | |||
vi /lib/systemd/system/mariadb.service | |||
and change | |||
ProtectHome=true | |||
to | |||
ProtectHome=false | |||
then run | |||
systemctl daemon-reload | |||
systemctl start mysql | |||
edits in /etc/mysql/mariadb.conf.d/50-server.conf | |||
To allow connections from remote machines | |||
#bind-address = 127.0.0.1 | |||
To start logging | |||
general_log_file = /var/log/mysql/mysql.log | |||
general_log = 1 | |||
== notes on root == | == notes on root == | ||
Nowadays when you are logged in to bash as root, you can enter mariadb by just running mariadb / mysql and will not be prompted for a password! However, you can also not enter mysql as root from another user or from phpmyadmin! [http://www.pontikis.net/blog/debian-9-stretch-rc3-web-server-setup-php7-mariadb] | Nowadays when you are logged in to bash as root, you can enter mariadb by just running mariadb / mysql and will not be prompted for a password! However, you can also not enter mysql as root from another user or from phpmyadmin! [http://www.pontikis.net/blog/debian-9-stretch-rc3-web-server-setup-php7-mariadb] | ||
Line 31: | Line 53: | ||
/etc/init.d/mysql start | /etc/init.d/mysql start | ||
</pre> | </pre> | ||
== moving the data directory == | |||
You can move the datadir to a new directory and then link to /var/lib/mysql, but the neater way is to change the datadir in /etc/mysql/ | |||
Also, if you want to move it to /home/mysql you need to edit /etc/systemd/system/mysqld.service | |||
[Service] | |||
ProtectHome=false | |||
If you want to move it to /usr or /boot you need | |||
[Service] | |||
ProtectSystem=off | |||
= Common Commands = | = Common Commands = |
Latest revision as of 11:28, 25 November 2020
Packages
apt-get install mysql-server phpmyadmin
From Debian 9 this will install MariaDB
Initial setup
run
mysql_secure_installation
to set up a root password and delete the test database
If you want the databases to run from the /home/ directory (or a subdir)
vi /lib/systemd/system/mariadb.service
and change
ProtectHome=true
to
ProtectHome=false
then run
systemctl daemon-reload systemctl start mysql
edits in /etc/mysql/mariadb.conf.d/50-server.conf
To allow connections from remote machines
#bind-address = 127.0.0.1
To start logging
general_log_file = /var/log/mysql/mysql.log general_log = 1
notes on root
Nowadays when you are logged in to bash as root, you can enter mariadb by just running mariadb / mysql and will not be prompted for a password! However, you can also not enter mysql as root from another user or from phpmyadmin! [1]
So to fix this, we enter mysql and do:
update mysql.user set plugin = 'mysql_native_password' where User='root'; flush privileges;
To reset the root password
/etc/init.d/mysql stop mysqld_safe --skip-grant-tables & MariaDB [(none)]> use mysql; MariaDB [mysql]> UPDATE user SET password=PASSWORD("new_password") WHERE User='root'; MariaDB [mysql]> FLUSH PRIVILEGES; MariaDB [mysql]> quit; /etc/init.d/mysql start
moving the data directory
You can move the datadir to a new directory and then link to /var/lib/mysql, but the neater way is to change the datadir in /etc/mysql/
Also, if you want to move it to /home/mysql you need to edit /etc/systemd/system/mysqld.service
[Service] ProtectHome=false
If you want to move it to /usr or /boot you need
[Service] ProtectSystem=off
Common Commands
Create a new database
create user user@localhost identified by 'somepass'; create database dbname; grant all on dbname.* to user@localhost; flush privileges;
Allow connections from other hosts
Preferably set % to a specific IP address
use mysql; update user set Host='%' where User='username'; update db set Host='%' where Db='dbname'; flush privileges;
Changing a user password
UPDATE user SET Password=PASSWORD('my_password') where USER='username'; flush privileges;
Delete a database
drop database dbname;
Don't forget to delete the database and user from the mysql User and Db tables