Monday, January 18, 2016

Deal with database MySQL


Best practice

http://dba.stackexchange.com/questions/76469/mysql-varchar-length-and-performance


Dump to .sql file

Whole database:
mysqldump -u root -p --databases bud >bud_database.sql

Chosen tables:

mysqldump -u root -p tinbds trangtin_district trangtin_estatetype trangtin_post trangtin_province trangtin_transtype > tinbds_trangtin.sql
-r output.sql


Import from .sql file

MySQL command line

CREATE DATABASE db-name;

then

mysql -u username -p database_name < file.sq


Backup and Restore

http://webcheatsheet.com/sql/mysql_backup_restore.php#compress


http://stackoverflow.com/questions/16287559/mysql-adding-user-for-remote-access

up vote145down voteaccepted
In order to connect remotely you have to have MySQL bind port: 3306 to your machines IP in my.cnf. Then you have to have created the user in both localhost and '%' wildcard and grant permissions on all DB's as such . See below:
my.cnf
from 5.7+:    edit /etc/mysql/mysql.conf.d/mysqld.cnf
#Replace xxx with your IP Address 
bind-address        = xxx.xxx.xxx.xxx
then
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';
Then
GRANT ALL ON *.* TO 'myuser'@'localhost';
GRANT ALL ON *.* TO 'myuser'@'%';
Depending on your OS you may have to open port 3306 to allow remote connections.
FLUSH PRIVILEGES;


Follow instructions (steps 1 to 3 don't needed in windows):
  1. Find mysql config to edit:
    /etc/mysql/my.cnf (Mysql 5.5)
    /etc/mysql/conf.d/mysql.cnf (Mysql 5.6+)
  2. Find bind-address=127.0.0.1 in config file change bind-address=0.0.0.0 (you can set bind address to one of your interface ips or like me use 0.0.0.0)
  3. Restart mysql service run on console: service restart mysql
  4. Create a user with a safe password for remote connection. To do this run following command in mysql (if you are linux user to reach mysql console run mysql and if you set password for root run mysql -p):
    GRANT ALL PRIVILEGES 
     ON *.* TO 'remote'@'%' 
     IDENTIFIED BY 'safe_password' 
     WITH GRANT OPTION;`
Now you should have a user with name of user and password of safe_password with capability of remote connect.

Change MySQL user password (MySQL >= 5.7)

after login in command line:

UPDATE mysql.user SET user='newusername',
 authentication_string=PASSWORD('newpassword') WHERE user='root'; FLUSH PRIVILEGES;
FLUSH PRIVILEGES;

Monitor

- http://stackoverflow.com/questions/568564/how-can-i-view-live-mysql-queries
- http://softwarerecs.stackexchange.com/questions/19886/free-options-to-mysql-query-analyzer


Memory Engine
https://www.domcop.com/blog/how-mysql-memory-table-saved-the-day/
http://stackoverflow.com/questions/10692398/how-do-i-make-a-mysql-database-run-completely-in-memory


http://webpy.readthedocs.org/en/latest/db.html

http://effbot.org/pyfaq/how-do-you-set-a-global-variable-in-a-function.htm



http://stackoverflow.com/questions/7522373/how-to-create-tables-with-password-fields-in-mysql

6 comments:

  1. http://stackoverflow.com/questions/2696884/split-value-from-one-field-to-two

    ReplyDelete
  2. http://stackoverflow.com/questions/13353549/making-mysql-in-clause-case-sensitive

    ReplyDelete
  3. http://stackoverflow.com/questions/16287559/mysql-adding-user-for-remote-access

    ReplyDelete
  4. http://theoryapp.com/select-random-records-in-mysql/

    ReplyDelete
  5. https://magp.ie/2012/10/26/quick-way-to-find-duplicate-entries-in-mysql-table/

    ReplyDelete
  6. https://www.askingbox.com/tip/mysql-change-minimum-word-length-for-full-text-search

    ReplyDelete