Difference between revisions of "Installing a new database server"

From Edgar BV Wiki
Jump to: navigation, search
(notes on root)
(Initial setup)
 
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=true
 +
then run
 +
  systemctl reload-daemon
 +
  systemctl start mysql
 +
 
== 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]

Latest revision as of 13:17, 13 June 2019

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=true

then run

  systemctl reload-daemon
  systemctl start mysql

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

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