yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm -y
yum install mysql-community-server -y
service mysqld start
service mysqld status
Generate Temporary password
grep 'temporary password' /var/log/mysqld.log
Login To MySQL
mysql -uroot -p
Reset Password and set Privilages
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Root@123';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'Root@123' WITH GRANT OPTION;
Open MySQL Port 3306
firewall-cmd --zone=public --add-port=3306/tcp --permanent
Similar in concept to templates – contain compiled code used to run common sql operations
Query only parsed once, but allows for multiple executions, with same or different parameters (performance consideration)
Related parameters do not need to be quoted (security consideration)
o Only feature pdo will emulate for adapters that do not support prepared statements
The mysqli_real_escape_string() function escapes special characters in a string for use in an SQL statement
• Combines individual sql operations into one
• Usually start with begin or begin transaction
• Execute the transaction using commit
• Cancel the transaction using rollback
START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; COMMIT;
Unbuffered MySQL queries execute the query and then return a resource while the data is still waiting on the MySQL server for being fetched. This uses less memory on the PHP-side, but can increase the load on the server. Unless the full result set was fetched from the server no further queries can be sent over the same connection. Unbuffered queries can also be referred to as “use result”.
Customers and admin are not able to login to magento 2 when memcached is enabled and to fix it increase the default value of innodb_buffer_pool_size in my.ini
A cursor can’t be used by itself in MySQL. It is an essential component in stored procedures. I would be inclined to treat a cursor as a “pointer” in C/C++, or an iterator in PHP’s foreach statement.
With cursors, we can traverse a dataset and manipulate each record to accomplish certain tasks. When such an operation on a record can also be done in the PHP layer, it saves data transfer amounts as we can just return the processed aggregation/statistical result back to the PHP layer (thus eliminating the select – foreach – manipulation process at the client side).
Since a cursor is implemented in a stored procedure, it has all the benefits (and limitations) of an SP (access control, pre-compiled, hard to debug, etc).
MySQL supports cursors inside stored programs. The syntax is as in embedded SQL. Cursors have these properties:
Asensitive: The server may or may not make a copy of its result table
Read only: Not updatable
Nonscrollable: Can be traversed only in one direction and cannot skip rows
Cursor declarations must appear before handler declarations and after variable and condition declarations.
MySQLi is an improved version of the older PHP MySQL driver, offering various benefits.
The developers of the PHP programming language recommend using MySQLi when dealing with MySQL server versions 4.1.3 and newer
The MySQLi extension provides various benefits with respect to its predecessor, the most prominent of which are:
- An object-oriented interface
- Object Mapping
- Support for prepared statements
- Support for multiple statements
- Support for transactions
- Enhanced debugging support
- Embedded server support
- More powerful Functionality
phpMyAdmin is a free and open source tool written in PHP intended to handle the administration of MySQL with the use of a web browser. It can perform various tasks such as creating, modifying or deleting databases, tables, fields or rows; executing SQL statements; or managing users and permissions.
1) Execute sudo apt-get install phpmyadmin
2) Edit the file /etc/apache2/apache2.conf. and include the line Include /etc/phpmyadmin/apache.conf
3) Restart Apache sudo /etc/init.d/apache2 reload
- Web interface
- MySQL database management
- Import data from CSV and SQL
- Export data to various formats: CSV, SQL, XML, PDF (via the TCPDF library), ISO/IEC 26300 – OpenDocument Text and Spreadsheet, Word, Excel, LaTeX and others
- Administering multiple servers
- Creating PDF graphics of the database layout
- Creating complex queries using Query-by-Example (QBE)
- Searching globally in a database or a subset of it
- Transforming stored data into any format using a set of predefined functions, like displaying BLOB-data as image or download-link
- Live charts to monitor MySQL server activity like connections, processes, CPU/Memory usage, etc.
- Working with different operating systems.
The mysql_fetch_array() function returns a row from a recordset as an associative array and a numeric array.
If two or more columns of the result have the same field names,then the last column will take precedence.