Frequently used mysql commands

mysql-backup.png

Mysql

Step 0: Read useful links

http://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/ https://www.digitalocean.com/community/tutorials/mysql-ru http://dba.stackexchange.com/questions/76788/how-to-create-database-with-charset-utf-8 http://ruhighload.com/post/%D0%9A%D0%B0%D0%BA+%D0%BD%D0%B0%D1%81%D1%82%D1%80%D0%BE%D0%B8%D1%82%D1%8C+MySQL+Master-Slave+%D1%80%D0%B5%D0%BF%D0%BB%D0%B8%D0%BA%D0%B0%D1%86%D0%B8%D1%8E

Step 1: Update your system by typing the following commands:

sudo apt update
sudo apt upgrade

Step 2: Install mysql version 5.7 on Ubuntu 16.04

sudo apt install mysql-server mysql-client

Step 3: Run mysql_secure_installation to secure your installation

sudo mysql_secure_installation

Step 4: How do I use MySQL server?

$ mysql -u root -p

Step 5: How do I create a new MySQL server database and user account?

CREATE DATABASE mydatabase CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

Grant privileges for user on database.table

GRANT [тип прав] ON [database's name].[table's name] TO [username]@'localhost;

Show user in MySQL:

mysql> select * from mysql.user;

or

mysql> select host, user, password from mysql.user;

Remove rights for user

REVOKE [тип прав] ON [database's name].[table's name FROM [username]@localhost;

Remove database from mysql server

DROP USER demo@localhost;

How to change password for some user in MySQL

SET PASSWORD = PASSWORD('passwordphrase')

Password for some user can be changed by this commands:

SET PASSWORD FOR username@localhost = PASSWORD('passwordphrase');
SET PASSWORD FOR username@"%" = PASSWORD('passwordphrase');

The same is can be done by these commands:

UPDATE mysql.user SET Password=PASSWORD('passwordphrase') WHERE User='логин' AND Host='localhost';
FLUSH PRIVILEGES;

or

GRANT USAGE ON БД.* TO username@localhost IDENTIFIED BY 'passwordphrase';

or

mysqladmin -u username password passwordphrase

How to Install SSH2 extension for PHP on Centos

ssh2.png

 
yum install gcc php-devel php-pear libssh2 libssh2-devel make
pecl install -f ssh2
echo extension=ssh2.so > /etc/php.d/ssh2.ini
service httpd restart
php -m | grep ssh2 

script that running unicorn for redmine

rdmine_unicorn_cent_nginx_.png

#!/bin/sh
/usr/bin/perl /root/scripts/rununicorn.pl

#! /usr/bin/perl -w
use strict;
use warnings;

print "running helpdesk\n";
$ENV{PATH}='/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/user/.local/bin:/home/user/bin:/home:/var/www/html/redmine/config';
print $ENV{'PATH'};
system ("BUNDLE_GEMFILE=/var/www/html/redmine/Gemfile bundle exec unicorn_rails -E production -c /var/www/html/redmine/config/unicorn.rb -D");

nginx as revers proxy

upstream redmine {
    server unix:/var/www/html/redmine/tmp/sockets/unicorn.sock;
    }

     server {
         listen 80;
         server_name example.com;
         client_max_body_size 200m;
         root /var/www/html/redmine/;

      location / {
          try_files $uri @ruby;
                  }

       location ~* \.(jpg|gif|png|js|css|ico)$ {
               root /var/www/html/redmine/public;
               expires 7d;
               error_page 404 502 504 = @ruby;
               }

        location @ruby {
        proxy_set_header  X-Real-IP  $remote_addr;
        proxy_set_header  X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header  Host $http_host;
        proxy_redirect off;
        proxy_read_timeout 300;
        proxy_pass http://redmine; #upstream redmine
        }
    }

GitHub – denisgolius

Denis

Ukraine

https://www.linkedin.com/in/denis-golius-1088b79b https://www.facebook.com/michaelweiss1234