Commands to export MySQL database schema, database with data, selective tables, ignoring tables
Tag: MySQL
Content Related to MySQL database
MySQL Table Size
Get MySQL table sizes using a query
SQL query results to CSV
Multiple times we get requests in projects to generate reports from database. Below command will be helpful in exporting the MySQL query results directly into CSV (comma separated values) file mysql -h {hostname} -u {username} -p {database_name} -B -e “SELECT code AS ‘Coupon Codes’ FROM salesrule_coupon WHERE rule_id=112” | sed “s/\t/,/g” > /tmp/coupon_code_$(date +%F).csv Above […]
MySQL table schema details
I am mentioning here a quick way to get the table schema format. Steps 1) Login to your database using mysql -u {username} -p -h {host_name> -D {database_name} 2) Execute query SELECT TABLE_NAME AS `Table`, ROW_FORMAT AS `Format` FROM information_schema.TABLES WHERE TABLE_SCHEMA = “{database_name}” 3) Primarily there are two types of format Dynamic and Compact […]
MySQLi with PHP
Below is the simple PHP script to connect database using mysqli Hope this is helpful.
Get database connection details in Magento
I am writing here an important code snippet for getting the database connection details used in Magento
Filters in Query in Magento
Hello All, When we are filtering data in Magento, time occurs when we want to fetch result after filters like Not equal, greater than, less than, etc. The addFieldToFilter method’s second parameter is used for this. It supports an alternate syntax where, instead of passing in a string, you pass in a single element Array. […]
MySQL Database restore
I have already listed the PHP script to generate backup of database in one of my earlier post. I am listing here the PHP script to restore database from mysql database file.
Insert and Retrieve file with MySQL
Some of you might be knowing that there is a functionality in MySQL database for storing files and retrieving the same. This is mainly used when we need to reduce response time for retrieving and using files in our application. I am listing here the method for inserting image file in and retrieving image file […]
Database Backup
I am listing here the PHP script to generate backup of database. For saving the file with ‘sql’ extension replace the code as specified below at specified line numbers in the above code (Although ‘zip’ file will take less space). You can also set cron jobs for dumping databdase in the cpanel of your site. […]