Last active
April 29, 2021 08:26
-
-
Save abhitechno01/d15d85929eabe1adab6d18e7936095ab to your computer and use it in GitHub Desktop.
MySQL commands
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| ########################################### | |
| copying database from ec2 instance to rds | |
| ########################################### | |
| Log into ec2 server using SSH | |
| Dump the database by using below command | |
| mysqldump -u username -p --databases db_name --master-data=2 --single-transaction --order-by-primary --force --column-statistics=0 > directory/db.sql | |
| remove definer from mysql dump | |
| sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' -i oldfile.sql | |
| comment out below line if exist | |
| CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000675', MASTER_LOG_POS=154; | |
| From same ec2 instance connect to RDS and import the db using the below command | |
| mysql -h amazon_rds_host -u username -p db_name < directory/db.sql | |
| note: db_name should be pre-created/exist in amazon_rds_host | |
| ################################# | |
| FIX: mysql slow import | |
| ################################# | |
| Reference 1: https://dba.stackexchange.com/questions/98814/mysql-dump-import-incredibly-slow-on-my-developers-machine/98815 | |
| Reference 2: https://stackoverflow.com/questions/2167522/innodb-takes-over-an-hour-to-import-600mb-file-myisam-in-a-few-minutes/2167641#2167641 | |
| Reference 3: https://dba.stackexchange.com/questions/83125/mysql-any-way-to-import-a-huge-32-gb-sql-dump-faster | |
| Login to mysql console from terminal | |
| ************************************** | |
| For importing sqls of more than 1 GB | |
| set global net_buffer_length=1000000; | |
| set global max_allowed_packet=1000000000; | |
| ************************************** | |
| before importing run the following commands | |
| SET autocommit=0; | |
| SET unique_checks=0; | |
| SET foreign_key_checks=0; | |
| innodb_buffer_pool_size = 4G | |
| innodb_log_buffer_size = 256M | |
| innodb_log_file_size = 1G | |
| innodb_write_io_threads = 16 | |
| innodb_flush_log_at_trx_commit = 0 | |
| Now database import part will come | |
| CREATE DATABASE <db_name>; | |
| use <db_name>; | |
| source file_name.sql; | |
| after import run the following command | |
| COMMIT; | |
| SET unique_checks=1; | |
| SET foreign_key_checks=1; | |
| at the end. | |
| ################################# | |
| Check the existing users in mysql | |
| ################################# | |
| $ mysql | |
| > use mysql | |
| > select * from user\G | |
| > select host, user, password from mysql.user; | |
| ################################################################## | |
| Create a new user, grant all privileges and reload configuration | |
| ################################################################## | |
| $ mysql | |
| > use mysql | |
| > CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'; | |
| > GRANT INSERT, SELECT, UPDATE ON *.* TO 'newuser'@'localhost'; | |
| OR | |
| > GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost'; | |
| > FLUSH PRIVILEGES; | |
| for more info on privileges checkout https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html | |
| OR | |
| > CREATE USER xyz@localhost IDENTIFIED WITH mysql_native_password BY 'passw0rd' | |
| and navigate to mysqlconfig file | |
| /etc/mysql/mysql.conf.d/mysqld.cnf | |
| and add default-authentication-plugin=mysql_native_password | |
| ######################## | |
| revoke user | |
| ######################## | |
| REVOKE ALL PRIVILEGES, GRANT OPTION FROM '<user>'@'localhost'; | |
| DROP USER '<user>'@'localhost'; | |
| ######################## | |
| Alter user's password | |
| ######################## | |
| $ mysql | |
| > use mysql | |
| > update user set password=PASSWORD("your-password") where User='existing-user'; | |
| OR | |
| > update user set authentication_string=password('your-password') where user='existing-user'; | |
| > FLUSH PRIVILEGES; | |
| ######################## | |
| Import and Export | |
| ######################## | |
| ####Export#### | |
| mysqldump -u <user_name> -p <db_name> > <local_path_with_filename_and_extension> | |
| ####Import#### | |
| login to mysql via command line | |
| mysql -u root -p | |
| create a database | |
| create database <db_name> | |
| exit from mysql using ctrl+D | |
| run below command for import | |
| mysql -u <user_name> -p <db_name> < <local_path_with_filename_and_extension> | |
| ################## | |
| NOTE | |
| ################## | |
| MySQL 5.7 changed the secure model: now MySQL root login requires a sudo. | |
| I.e., phpMyAdmin will be not able to use root credentials. | |
| The simplest, safest and permanent solution will be create a new user and grant required privileges. | |
| reference: https://askubuntu.com/questions/763336/cannot-enter-phpmyadmin-as-root-mysql-5-7 | |
| ############################ | |
| FIX: phpmyadmin count error | |
| ############################ | |
| Warning in ./libraries/plugin_interface.lib.php#551 | |
| count(): Parameter must be an array or an object that implements Countable | |
| replace | |
| if ($options != null && count($options) > 0) { | |
| with | |
| if ($options != null && count(array($options)) > 0) { | |
| Sometimes the error can in ./libraries/sql.lib.lib.php#613 | |
| Replace: | |
| ((empty($analyzed_sql_results['select_expr'])) | |
| || (count($analyzed_sql_results['select_expr'] == 1) | |
| && ($analyzed_sql_results['select_expr'][0] == '*'))) | |
| With: | |
| ((empty($analyzed_sql_results['select_expr'])) | |
| || (count($analyzed_sql_results['select_expr']) == 1) | |
| && ($analyzed_sql_results['select_expr'][0] == '*')) | |
| Better solution would be to instruct phpmyadmin to use php version that doesn't treat | |
| count function to have an countable object as a parameter |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment