Things to consider when migrating from Oracle to MySQL

There are actually more things to consider when migrating from Oracle to MySQL than we need to sort out the technical details of data type conversion, but also more important.

There are two issues to consider in advance:

Why migrate from Oracle?

Why migrate to MySQL

If you answer the above two questions, you will basically straighten out the whole thing. I will explain it in a basic and objective manner.

Question 1: Why should we migrate from Oracle?

Judging from the practice of the industry (mainly the Internet industry), this matter is definitely not triggered from the technical feasibility, but from the business feasibility. In the final analysis, the main starting point is two words: cost .

MySQL is free and open source, and more importantly, it has sufficient industry practice verification, so it has a unique advantage. Ali’s visit to IOE many years ago has become a benchmark in the industry.

The cost thing is very delicate and can’t be explained in a few words. For example, you can use Office to work. Of course, you may consider buying a license or green version activation. However, if you use WPS, it is justified. Of course, there are some gaps in function with Office, but there will not be some blurred boundaries.

From another dimension of business, think about the Internet industry we are in contact with. In addition to recharging and money-related businesses, many of the business requirements for data integrity will be reduced by one dimension. Many of the problems that money can solve Not a problem, what is more important than money, I think it should be safe, safety includes life safety, industry safety, system safety, these are absolutely not allowed to have some major problems, and these influences are too large, such as hospital doctors giving patients According to the data of medicines, these influences are very large, and if something goes wrong, it is easy to become a public event. Taking financial-grade business as a demarcation point, the security field is above, and the fields below are actually some optional spaces, which are very large. One of the reasons for choosing a business is also here. There are technical pockets. These costs are also a binding relationship for manufacturers. It’s a tragedy if you can’t find professional quick support if something goes wrong.

Then there is open source customization. In fact, many open source technologies have different open source protocols. We also need to consider the boundaries and scope of these protocols when we adopt open source technologies.

So what needs to be clear at this point is:

1. Cost factors need to be weighed, definitely not black and white

2. Migrating to MySQL is actually not the ultimate solution, just an optional solution

3. Sufficient accumulation of open source technology, strong technical control ability

4. The essence of migration is to find the most suitable business scenario, not to realize it for technical realization

For the fourth point, for example, Oracle has no doubt in performance, but if there are massive read requests, it is actually not suitable for Oracle to carry, of course, it is not suitable for MySQL, maybe Redis’s solution will be better some.

Question 2: Why should you migrate to MySQL

To answer this question, in fact, our main line is what MySQL can do.

The first is cost. Open source is free and easy to customize. MySQL’s options are definitely not only community edition, but also a series of branches, such as Percona branch, MariaDB branch, storage engine InnoDB, MyRocks, etc. are all free and optional.

The second is that MySQL is efficient and lightweight enough. The efficiency of MySQL is very short in terms of use, the learning cycle will be short, easy to get started, and the resource requirements of the system are not high.

The third is the horizontal expansion ability. It is easier to understand Oracle as a subway and MySQL as a bus. We can easily add a bus line, but adding a subway line is completely different. I think this is a core point of migrating to MySQL, which is why many Internet MySQL scales are hundreds of thousands, and the explosive growth of the business. MySQL’s ability to expand is not reflected in the MySQL database itself, but for the architecture In terms of scalability, this is one reason why many MySQL DBAs are relatively expensive.

The fourth is replication, which is a bright spot of MySQL compared to Oracle. If you need to do cross-data center replication, there is a certain delay. It is easy to use MySQL’s native replication solution. MySQL supports many different dimensions. Copy scheme.

The fifth is the light dependence of business. This can be divided into two dimensions. One is functional limitation and the other is performance limitation. This itself is a lack of MySQL functions and performance, but it is an advantage because to support distributed requirements, the dependence of the business on the database needs to be more light and light, and the stored procedures that originally supported poorly can be naturally weakened.

The sixth is the ecosystem brought by open source. The open source dividend brings enterprises a lot of choices in technical solutions, so that things that originally need to be paid for become us to use.

Question 3: Things to consider when migrating from Oracle to MySQL

The first is the difference in architecture. The difference between Oracle and MySQL is still relatively large. Of course, Oracle can also use a synonymous architecture to achieve a MySQL-like access model.

The difference in data type is actually more specific technical details, I will give some supplements.

Null and empty strings in oracle can be handled as null, but the two are different in MySQL.

The oracle table name and user name are limited in length. Within 30 characters, the length is much larger in MySQL, especially the table name needs attention.

Oracle will handle them in uppercase by default, and MySQL is case sensitive by default.

For MySQL type, there are many details to be considered in MySQL, such as numeric type, oracle number to get, MySQL has a series of numeric types to choose from, it is not recommended to adapt the big int to all needs.

To answer the notes more clearly, it can be categorized as a question: What is less MySQL than Oracle?

There must be differences in performance, we mainly take care of the function. The principle of comparison is not to say that Oracle must have MySQL, but that it has better usage characteristics from some usage scenarios.

There is limited support for stored procedures. This is the technical debt of many companies. It is easy to deal with it. For example, stored procedures, it is necessary to use stored procedure calls to dock, and the future is endless.

No synonyms

Without db link, this feature is not supported in MySQL is actually a good thing, to eliminate the need for cross-database association.

Without sequence, this self-increasing column of MySQL can make up for it.

Without materialized views, it is difficult to achieve the requirement of incremental refresh.

Partition tables are available, but they are rarely used

The optimizer is weak, multi-table association, Hash Join is still a weak item in MySQL.

The difference between indexes and the realization of covering indexes are also very different.

The performance of bound variables is not much different. The problem of sensitive bound variables in Oracle is not a problem in MySQL.

Performance tools, performance tools in MySQL are relatively few, and the granularity and effect are limited.

Thanks for reading this post.

Pankaj K.