2016 May

Common MySQL / MariaDB Commands

Log into mysql client:

General Linux

# mysql -u username -p
 Enter password: ****
 mysql> _

Plesk – Linux:

mysql -uadmin -p`cat /etc/psa/.psa.shadow `

Plesk – Windows: (Untested)

C:\Program Files\Parallels\Plesk\admin\bin>dbclient.exe --direct-sql --sql="select * from databaseservers where type='mysql' and host='localhost'"

WHM / cPanel

mysql -uroot

If this does not work, you might try using the root password or you may have to ask for it.

 

Backup a Database

To create a backup of a single database, run the following from the command line:

mysqldump -u username -p [password] database > database.sql

 

To create a backup of all databases, run the following from the command line:

mysqldump -u username -p [password] –-all-databases > filename

 

Restore a Database

To restore a single database:

mysql -u username -p [password] database < database.sql

* If the database already exists, you may need to edit the .sql file and comment out ( — [add 2 hyphens before] ) the ‘CREATE DATABASE database;’ line near the top of the file

If this does not work, try remove the database first:

mysql -u user -p [password]
DROP DATABASE database;
CREATE DATABASE database;
EXIT

Now try again

 

Restore a table

mysqlimport -u user -p [password] database databasetable.sql

Create a new database

mysql> CREATE DATABASE database_name;

Create Users

To create a new user on a database:

mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

Note: keep all single quotes exactly as shown.

To create a new Super User for all databases:

mysql>  GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

 

MySql Query Commands

USE DATABASE dbname;
ALTER TABLE $table DROP COLUMN $fieldName";
ALTER TABLE $table CHANGE COLUMN $oldFieldName $newFieldName;
ALTER TABLE $table ADD COLUMN $newFieldName TINYINT UNSIGNED NOT NULL AFTER $existingFieldName;
ALTER TABLE $table ADD INDEX $indexName($fieldName);
UPDATE $tableName SET $column1="value1", $column2="value2" WHERE $columnA="valueA" AND $columnB="valueB";
INSERT INTO $tableName ($column1, $column2) VALUES ($value1, $value2);

Export a MySql query data to a .csv file

{insert full query here} INTO OUTFILE '/tmp/querydata.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

Import a .csv file to a table

mysqlimport --ignore-lines=1 \
            --fields-terminated-by=, \
            --columns='ID,Name,Phone,Address' \
            --local \
            -u root -p \
            Database \
            /path/to/tableName.csv

For a tab delimiter, use: –fields-terminated-by=’\t’

MySQL Engines

Use the following code to see which database engines are allowed. Some of these engines include:

mysql> SHOW ENGINES;

MyISAM Default engine as of MySQL 3.23 with great performance
MEMORY Hash based, stored in memory, useful for temporary tables
InnoDB Supports transactions, row-level locking, and foreign keys
BerkeleyDB Supports transactions and page-level locking
BLACKHOLE /dev/null storage engine (anything you write to it disappears)
EXAMPLE Example storage engine
ARCHIVE Archive storage engine
CSV CSV storage engine
ndbcluster Clustered, fault-tolerant, memory-based tables
FEDERATED Federated MySQL storage engine
MRG_MYISAM Collection of identical MyISAM tables
ISAM Obsolete storage engine

MySql Version

mysql --version

Or, you will see the version as soon as you log in.

.

By admin on May 29, 2016 | MySQL - MariaDB | A comment?

Install ownCloud Server on Ubuntu 16.04

Software Installation

Install Apache web server and dependancies

sudo apt-get -y install apache2 libapache2-mod-php7.0

Install PHP and required supporting modules

sudo apt-get -y install php php-mysql php-curl php-dom php-gd php-ldap php-mcrypt php-zip

Install MariaDB database server

sudo apt-get install mariadb-server mariadb-client

Install ownCloud server

Download the current ownCloud software

cd /var/www/html
sudo wget https://download.owncloud.org/community/owncloud-9.0.1.tar.bz2

Extract the compressed file to the ownCloud folder

sudo tar -xjvf owncloud-9.0.1.tar.bz2

Change the installation’s file permissions to use the Apache user

sudo chown -R www-data:www-data owncloud

 

Database Configuration

Configure MariaDB

Running the following command and answering the associated prompts will help secure the database.

sudo 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): Enter
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] Enter
New Password: RootDatabasePassword
Re-enter new password: RootDatabasePassword
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] Enter
 ... 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] Enter
 ... 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] Enter
 - 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] Enter
 ... 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!

Create the ownCloud database user and database

sudo mysql -u root -p
Enter password: RootDatabasePassword
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is ##
Server version: 5.5.47-MariaDB MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database owncloud;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create user 'owncloud'@'localhost' identified by 'OwnCloudDatabaseUserPassword';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant all on owncloud.* to 'owncloud'@'localhost';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit

 

Configure Apache

 

owncloud.domain.com Method

Use this method if you will be accessing your ownCloud installation through a domain name vs. using an IP address as shown above.

Create the folder to store the SSL files

sudo mkdir /etc/apache2/ssl
cd /etc/apache2/ssl

Create the CSR (certificate signing request)

In order to add instant security before purchasing an SSL (Secure Socket Layer) certificate, you’ll need to create a self-signed certificate.

Use the following link to create the CSR.
http://www.howtowebhost.com/how-to-create-a-csr-certificate-signing-request-in-linux-using-openssl/

Create the Self-Signed Certificate

*Make sure you use the same files names you created in the previous steps!

openssl x509 -req -days 365 -in owncloud.domain.com.csr -signkey owncloud.domain.com.key -out owncloud.domain.com.crt

Configure Apache to use listen on Port 443

sudo a2enmod ssl
sudo service apache2 restart

Add the following lines to the Apache config file

sudo nano /etc/apache2/sites-available/owncloud.domain.com.conf
<VirtualHost *:80>
    ServerAdmin     youremail@domain.com
    ServerName      owncloud.domain.com
    DocumentRoot    /var/www/html/owncloud
    ErrorLog        /var/log/apache2/owncloud-error.log
    TransferLog     /var/log/apache2/owncloud-access.log
    LogLevel warn
</VirtualHost>
<VirtualHost *:443>
    ServerAdmin     youremail@domain.com
    ServerName      owncloud.domain.com
    DocumentRoot    /var/www/html/owncloud

    SSLEngine on
    SSLCertificateFile     /etc/apache2/ssl/owncloud.domain.com.crt
    SSLCertificateKeyFile  /etc/apache2/ssl/owncloud.domain.com.key

    ErrorLog        /var/log/apache2/owncloud-error.log
    TransferLog     /var/log/apache2/owncloud-access.log
    LogLevel warn
</VirtualHost>

Save the file

Ctrl+X
Save modified buffer (ANSWERING "No" WILL DESTROY CHANGES) ? Y
File Name to Write: /etc/httpd/conf.d/owncloud.conf Enter

Enable the site

sudo a2ensite owncloud.domain.com.conf
sudo service apache2 reload

 

Configure ownCloud

Stop Apache

Prevent anyone from accidentally configuring ownCloud before you finish.

sudo service apache2 stop

Create the data folder

By default, ownCloud stores the files in the /data folder off of the primary website. Misconfigurations with the web server, or other issues could make the files in this folder accessible to malicious users, so we’re going to move the data to a folder outside of ownCloud’s web space.

sudo mkdir /home/ocdata
sudo chown -R www-data:www-data /home/ocdata

Setup ownCloud’s initial configuration

sudo -u www-data php /var/www/html/owncloud/occ maintenance:install --database "mysql" --database-name "owncloud" --database-user "ownCloudDBUser" --database-pass "ownCloudDBPassword" --admin-user "ownCloudAdmin" --admin-pass "ownCloudAdminPassword" --data-dir "/home/ocdata"
ownCloud is not installed - only a limited number of commands are available
ownCloud was successfully installed

* The above command took about 30 seconds to complete on my system

Enable Encryption

The following commands will enable whole-site encryption on all data uploaded to the server.

Enable ownCloud’s encryption application

sudo -u www-data php /var/www/html/owncloud/occ app:enable encryption

Create the Master Key

sudo -u www-data php /var/www/html/owncloud/occ encryption:enable-master-key
Warning: Only available for fresh installations with no existing encrypted data! There is also no way to disable it again. Do you want to continue? (y/n) Y Enter
Master key successfully enabled.

Enable all-user encryption

sudo -u www-data php /var/www/html/owncloud/occ encryption:encrypt-all

Add your domain to the “Trusted Domains”

sudo nano /var/www/html/owncloud/config/config.php

Add the following line and save the file:

  'trusted_domains' => 
  array (
    0 => 'localhost',
    1 => 'owncloud.domain.com',
  ),

Setup Security

At this point, your ownCloud server is ready to use. You can log in with your admin user and create other users, upload and download files, etc. However, we recommend taking a few additional steps to help secure your data from malicious users.

Change the data storage folder

By default, ownCloud stores the files in the /data folder off of the primary website. Misconfigurations with the web server, or other issues could make the files in this folder accessible to malicious users, so we’re going to move the data to a folder outside of ownCloud’s web space.

Stop the webserver

sudo service apache2 stop

Create the new data folder

sudo mkdir /home/ocdata
sudo cp -R /var/www/html/owncloud/data/. /home/ocdata/
sudo chown -R www-data:www-data /home/ocdata

Set the path in the config file

sudo nano /var/www/html/owncloud/config/config.php

Make the following edits and save the file:

  # 'datadirectory' => '/var/www/html/owncloud/data',
  'datadirectory' => '/home/ocdata',

Support Issues

Issue: WebDav errors and unable to use the desktop client.

Solution: Desktop Client version out of date

Check the Apache access log. if you see something like:

"PROPFIND /remote.php/webdav/ HTTP/1.1" 403 220

You should manually upgrade your desktop client to version 1.7 or higher

Ubuntu 14.04

1. Uninstall the current client using Ubuntu’s software package manager.
2. Run the following commands:

sudo sh -c "echo 'deb http://download.opensuse.org/repositories/isv:/ownCloud:/desktop/Ubuntu_14.04/ /' >> /etc/apt/sources.list.d/owncloud-client.list"
sudo apt-get update
sudo apt-get install owncloud-client
By admin on May 6, 2016 | ownCloud | A comment?