Update root password MySQL
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
Find duplicate rows MySQL
A nice simple query to find duplicate rows in MySQL: SELECT COLUMN_NAME, COUNT(*) c FROM TABLE GROUP BY COLUMN_NAME HAVING c > 1; Credit to the Tin Man
Create MySQL docker container and access externally
If you have a fairly simple setup with one network card and would like a MySQL container bound to both the internet docker IP and the IP of your network card you can run the following: docker run -p 0.0.0.0:33060:
Change/update MySQL password
To change the MySQL password for a particular user first login to MySQL: mysql -u root -p Next update the appropriate user's password with the following command: SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NEW_PASSWORD');
Disable/Enable MySQL Password Validation
If you are having issues with MySQL password validation, you can run the following commands to disable it: mysql -u root -p uninstall plugin validate_password; Should you want to enable MySQL validation again you can run this command once logged into MySQL: INSTALL
Backup / Restore MySQL database Docker container
Backup docker exec CONTAINER /usr/bin/mysqldump -u root --password=root DATABASE > backup.sql Restore cat backup.sql | docker exec -i CONTAINER /usr/bin/mysql -u root --password=root DATABASE
Replace text in MySQL query
If you'd like to replace the text in a MySQL column you can run the following query: UPDATE tableName SET columnName = REPLACE(columnName, 'old text to replace', 'new text to replace with') WHERE columnName LIKE '%search_term%'; LIKE '%search_term%'
Useful commands to setup MySQL in Docker
Create a MySQL Docker container docker run --name my-container-name -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql Login to Docker container bash / terminal docker exec -it mysql bash Create MySQL database in Docker In order to create the database you'll first need to login to
Export MySQL results to outfile/CSV
To export MySQL results to an outfile or CSV file: SELECT column_name FROM table_name INTO OUTFILE '/var/lib/mysql-files/export.csv'; If you receive the following error: The MySQL server is running with the --secure-file-priv option so it cannot execute this
Solve error 1290 --secure-file-priv MySQL
If you're encountering the following error: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement This is generally as a result of exporting results to a directory not deemed secure by MySQL. To solve this you can find
Import single table into MySQL
To import a single table into MySQL: mysql -u username -p database_name < table_name.sql View related article Export single table MySQL
Export single table MySQL
If you receive the following error: Error 2020: Got packet bigger than max_allowed_packet bytes when dumping table This normally occurs as a result of a particular table exceeding the max_allowed_packet size. This can be solved by exporting a single table
