OQMD2PyChemiaDB
From WVU Materials Discovery Group
Contents
Recreating the OQMD database and PyChemiaDB
Install MariaDB
If the system is running RHEL/CentOS The commands below assume that you have become root, otherwise use sudo before each command
sudo yum install mariadb-server mariadb mariadb-test
Activate MariaDB service
To make the service available inmediately as well as in any future restart of the machine
systemctl start mariadb.service systemctl enable mariadb.service
Check the service is actually running
systemctl status mariadb.service
The answer should be something like:
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Active: active (running) since Mon 2017-08-14 20:46:09 EDT; 31s ago
Main PID: 8439 (mysqld_safe)
CGroup: /system.slice/mariadb.service
├─8439 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
└─8596 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/va...
Aug 14 20:46:07 mdg16.wvu.edu mariadb-prepare-db-dir[8357]: The latest information about MariaDB is available at http://mariadb.org/.
Aug 14 20:46:07 mdg16.wvu.edu mariadb-prepare-db-dir[8357]: You can find additional information about the MySQL part at:
Aug 14 20:46:07 mdg16.wvu.edu mariadb-prepare-db-dir[8357]: http://dev.mysql.com
Aug 14 20:46:07 mdg16.wvu.edu mariadb-prepare-db-dir[8357]: Support MariaDB development by buying support/new features from MariaDB
Aug 14 20:46:07 mdg16.wvu.edu mariadb-prepare-db-dir[8357]: Corporation Ab. You can contact us about this at sales@mariadb.com.
Aug 14 20:46:07 mdg16.wvu.edu mariadb-prepare-db-dir[8357]: Alternatively consider joining our community based development effort:
Aug 14 20:46:07 mdg16.wvu.edu mariadb-prepare-db-dir[8357]: http://mariadb.com/kb/en/contributing-to-the-mariadb-project/
Aug 14 20:46:07 mdg16.wvu.edu mysqld_safe[8439]: 170814 20:46:07 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
Aug 14 20:46:07 mdg16.wvu.edu mysqld_safe[8439]: 170814 20:46:07 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
Aug 14 20:46:09 mdg16.wvu.edu systemd[1]: Started MariaDB database server.
You can also confirm that you can get initial access from the command line
$ mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.52-MariaDB MariaDB Server Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) MariaDB [(none)]> quit Bye
Secure the installation
There is a basic securing script that will associate a password to the root account and removing the test database entirely.
Just press enter when asked for the root password inside MariaDB. That root account have nothing to do with the root account of the system, the passwords does not need to match.
$ mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n]
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n]
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n]
... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n]
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n]
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
Creating an non root user with privileges on the database
Assuming that there is a user called 'mdg' we can give that user priviledges to read and write all databases running on the system. You can also filter to be just one database and limit to just read.
$ mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 12 Server version: 5.5.52-MariaDB MariaDB Server Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> CREATE USER 'mdg'@'localhost'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> GRANT ALL PRIVILEGES ON * . * TO 'mdg'@'localhost'; FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) MariaDB [(none)]> quit Bye
Creating a New database
Lets create a new database called oqmd that will be using to recreate from the dump
$ mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 16 Server version: 5.5.52-MariaDB MariaDB Server Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> create database oqmd; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> use oqmd; Database changed MariaDB [oqmd]> quit Bye
Recreating the OWMD database from the dump
The webpage for the OQMD database is http://oqmd.org The most recent database can be download from
By the time this tutorial was written the line to download the most recent database is
wget http://oqmd.org/static/downloads/qmdb__v1_1__102016.sql.gz
Once you download the compress dump, uncompress it with
gunzip qmdb__v1_1__102016.sql.gz
Once the file is uncompress recreate the new database with:
mysql oqmd -u root -p < qmdb__v1_1__102016.sql
Once the database is created you can test the number of entries
$ mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 21 Server version: 5.5.52-MariaDB MariaDB Server Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> use oqmd; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [oqmd]> SELECT COUNT(*) FROM entries; +----------+ | COUNT(*) | +----------+ | 471857 | +----------+ 1 row in set (0.07 sec) MariaDB [oqmd]> quit Bye
The OQMD database is ready and we can continue creating the PyChemiaDB database.
