If you have problem accessing your MySQL database with this error
ERROR 1045: Access denied for user: ‘root@localhost’ (Using password: NO)
You can try reset your root password with the following steps:
1. Create a txt file with this content
UPDATE mysql.user SET Password=PASSWORD('newpassword') WHERE User='root'; FLUSH PRIVILEGES;
2. Save the file as mysql-init.txt in c:\ (or any directory you want)
3. Stop MySQL service
4. Start MySQL service in command prompt with this command line
5. Remove the txt file in created in step 2
Note: You can/need to specify correct path for the file and mysql directory for step 2 and 4.
To backup mysql database (all databases) can use this batch file
Then you can compile the .bat file to become .exe file so that the password is hidden.
Lastly, you can include the exe file to run in task scheduler
You can have option to upload the file via FTP to other server
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
:: Start FTP files to remote server :: Create the temporary FTP script file > script.ftp ECHO ftpusername >>script.ftp ECHO ftppassword >>script.ftp ECHO lcd / >>script.ftp ECHO lcd /MySQLBackups/backupfiles >>script.ftp ECHO binary >>script.ftp ECHO prompt n >>script.ftp ECHO put FullBackup.%backupdate%.zip >>script.ftp ECHO bye :: Use the temporary script for unattended FTP :: Note: depending on your OS version you may have to add a '-n' switch FTP -v -s:script.ftp ftphostcom.com :: Overwrite the temporary file before deleting it TYPE NUL >script.ftp DEL script.ftp :: DONE FTP backup file to remote server
To update a table with a condition within other table (to join table), you can use this statement
1 2 3
UPDATE table1 a, table2 b SET a.field6 = value WHERE a.field1 = b.field1 AND b.field2 = value2
This SQL statement to be used to insert data into a table from another different table.
1 2 3
INSERT INTO table1 (field1) SELECT table2.field2 FROM table2 WHERE table2.field2 > 100;
Be careful with ambiguous fields. So put the table name in front of the field name.
Also be careful with duplicate data inserted to the table.
If you want to update all records can use this sql statement
UPDATE staff SET `staff_name` = UPPER( `staff_name` )
Here is the link where you can download IP to country sql for database. Also come with some sample codes on how to use the data. Might be useful for your program
I faced problem uploading a huge sql file to my shared hosting server. The size was not that big. It was only 1.8MB in size with about 30,000 records but phpMyAdmin (provided in cpanel) still failed to fully upload and run the file.
I then googled for a solution.
One of the solutions suggest to use SSH but I’m not familiar with it.
Another solution is by using mysqldumper but the configuration seems quite tedious.
Then I found another solution called BigDump which is very straight forward. You upload a PHP script together with your huge sql file via FTP. Then just run the PHP script as usual. In a second all the data has been restored in your database.
You will need this SQL statement to list all duplicate contents in a table
SELECT email, COUNT(email) AS NumOccurrences FROM users GROUP BY email HAVING ( COUNT(email) > 1 )
If you want to list all duplicate records
SELECT t1.* FROM `some_table` t1, ( SELECT documentno, COUNT(1) AS dup FROM `some_table` GROUP BY documentno HAVING dup > 1 ) AS t2 WHERE t1.documentno = t2.documentno;
Then how to remove the duplicate records?
1. Create new table and insert clean records (without duplicate)
CREATE TABLE new_table AS SELECT * FROM old_table WHERE 1 GROUP BY [COLUMN TO remove duplicates BY];
Note: Be careful, this will create a table without primary key, auto-increment etc. So make sure you update your table after run this statement
2. Drop old table (with duplicates)